There are additional considerations for sortkey.
sortkey, a system function, generates values that can be used to order results based on collation behavior. This allows you to work with character collation behaviors beyond the default set of Latin-character-based dictionary sort orders and case- or accent-sensitivity. The return value is a varbinary datatype value that contains coded collation information for the input string that is returned from the sortkey function.
For example, you can store the values returned by sortkey in a column with the source character string. Ro retrieve the character data in the desired order, include in the select statement an order by clause on the columns that contain the results of running sortkey.
sortkey guarantees that the values it returns for a given set of collation criteria work for the binary comparisons that are performed on varbinary datatypes.
sortkey can generate up to six bytes of collation information for each input character. Therefore, the result from using sortkey may exceed the length limit of the varbinary datatype. If this happens, the result is truncated to fit. Since this limit is dependent on the logical page size of your server, truncation removes result bytes for each input character until the result string is less than the following for DOL and APL tables:
|
Locking Scheme |
Page Size |
Maximum Row Length |
Maximum Column Length |
|---|---|---|---|
|
APL tables |
2K (2048 bytes) |
1962 |
1960 bytes |
|
4K (4096 bytes) |
4010 |
4008 bytes |
|
|
8K (8192 bytes) |
8106 |
8104 bytes |
|
|
16K (16384 bytes) |
16298 |
16296 bytes |
|
|
DOL tables |
2K (2048 bytes) |
1964 |
1958 bytes |
|
4K (4096 bytes) |
4012 |
4006 bytes |
|
|
8K (8192 bytes) |
8108 |
8102 bytes |
|
|
16K (16384 bytes) |
16300 |
16294 bytes If table does not include any variable length columns |
|
|
16K (16384 bytes) |
16300 (subject to a max start offset of varlen = 8191) |
8191-6-2 = 8183 bytes If table includes at least on variable length column.* |
|
|
* This size includes six bytes for the row overhead and two bytes for the row length field. |
|||
If this occurs, the SAP ASE server issues a warning message, but the query or transaction that contained the sortkey function continues to run.
char_expression or uchar_expression must be composed of characters that are encoded in the server’s default character set.
char_expression or uchar_expression can be an empty string. If it is an empty string, sortkey returns a zero-length varbinary value, and stores a blank for the empty string.
An empty string has a different collation value than an NULL string from a database column.
If char_expression or uchar_expression is NULL, sortkey returns a null value.
If a unicode expression has no specified sort order, the SAP ASE server uses the binary sort order.
If you do not specify a value for collation_name or collation_ID, sortkey assumes binary collation.
The binary values generated from the sortkey function can change from one major version to another major version of SAP ASE, such as version 12.0 to 12.5, version 12.9.2 to 12.0, and so on. If you are upgrading to the current version of SAP ASE, regenerate keys and repopulate the shadow columns before any binary comparison takes place.