17.05 - Sorting and Default Sort Order Row Length Errors - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
Before performing the sort operation that orders the rows to be returned, the database creates a sort key which is appended to the rows. If the length of the sort key exceeds the system row maximum length of 1 MB, the operation returns an error. Depending on the situation, the error message text is one of the following.
  • A data row is too long.
  • Maximum row length exceeded in database_object_name.

For explanations of these messages, see Teradata Vantage™ - Database Messages, B035-1096.

There are several possible reasons why a data row plus BYNET sort key can unexpectedly exceed the spool row size limit of 1 MB, even without including any updates.
  • The value for the MaxDecimal DBS Control field for your system might have been changed to a larger value, which could increase the number of bytes stored for each value in a DECIMAL column to as much as 38 bytes where it might previously have been only 1, 2, 4, or 8 bytes. See Teradata Vantage™ - Database Design, B035-1094 and Teradata Vantage™ - Data Types and Literals, B035-1143.
  • Your site has upgraded from a system with a 32-bit aligned row architecture to a system with a 64-bit architecture. This upgrade expands the size of data, both on disk and in memory, for a number of data types. The expansion is due to byte alignment restrictions that require padding to ensure alignment on 8-byte boundaries. These restrictions do not exist for 32-bit architectures. Therefore, the same row can be within the spool row 1 MB size boundary on a 32-bit system, but exceed that limit on a 64-bit system.

    This issue is not relevant if your system uses the packed64 row architecture rather than the aligned row format architecture. For more information, see Teradata Vantage™ - Database Design, B035-1094.

By default, nulls sort before all other data in a column. In other words, nulls sort low. For example, suppose you want to sort the following set of values: 5, 3, null, 1, 4, and 2.

The values sort as follows:

     null
     1
     2
     3
     4
     5

By default, the result values of a character expression are sorted in ascending order, using the collating sequence in effect for the session.