Selecting the Default Unicode Sort Order

The value for default unicode sort order database option is different than the sort order for the server’s default character set.

This separate configuration parameter is a static parameter that requires that you restart your server and reindex the unichar data if it is changed. This sort order is identified using a string parameter, rather than a numeric parameter, to guarantee that the sort order is unique.

This table lists the available default Unicode sort orders:

Name

ID

Description

defaultml

20

Default Unicode multilingual ordering

thaidict

21

Thai dictionary ordering

iso14651

22

Ordering as per ISO14651 standard

utf8bin

24

Ordering for UTF-16 that matches the UTF-8 binary

binary

25

Binary sort

altnoacc

39

Alternate accent-insensitive

altdict

45

Alternate dictionary ordering

altnocsp

46

Alternate case-insensitive with preference

scandict

47

Scandinavian dictionary ordering

scannocp

48

Scandinavian case-insensitive with preference

bin_utf8

50

UTF-8 binary sort order

dict

51

General-purpose dictionary ordering

nocase

52

General-purpose case-insensitive dictionary ordering

nocasep

53

General-purpose case-insensitive with preference

noaccent

54

Dictionary order, case-insensitive, accent-insensitive

espdict

55

Spanish dictionary ordering

espnocs

56

Spanish case-insensitive dictionary ordering

espnoac

57

Spanish accent-insensitive dictionary ordering

rusnocs

59

Russian case-insensitive dictionary ordering

cyrnocs

64

Cyrillic case-insensitive dictionary ordering

elldict

65

Greek dictionary ordering

hundict

69

Hungarian dictionary ordering

hunnoac

70

Hungarian accent-insensitive dictionary ordering

hunnocs

71

Hungarian case-insensitive dictionary ordering

turknoac

73

Turkish accent-insensitive dictionary ordering

This table lists the loadable sort orders:

Name

ID

Description

cp932bin

129

Ordering that matches the binary ordering of CP932

dynix

130

Chinese phonetic ordering

gb3213bn

137

Ordering that matches the binary ordering of GB2312

cyrdict

140

Common cyrillic dictionary ordering

turdict

155

Turkish Dictionary ordering

euckscbn

161

Ordering that matches the binary ordering of EUCKSC

gbpinyin

163

Chinese phonetic ordering

rusdict

165

Russian dictionary ordering

sjisbin

179

Ordering that matches the binary ordering of SJIS

eucjisbn

192

Ordering that matches the binary ordering of EUCJIS

big5bin

194

Ordering that matches the binary ordering of BIG5

To view this sort order list in SAP ASE, use sp_helpsort. See the Reference Manual: Procedures.

You can add sort orders using external files in the $/collate/Unicode directory. The names and collation IDs are stored in syscharsets. The names of external Unicode sort orders do not have to be in syscharsets before you can set the default Unicode sort order.

Note: External Unicode sort orders are provided by SAP. Do not attempt to create external Unicode sort orders.

Sort order associated with Unicode data is completely independent of the sort order associated with traditional character data. All relational expressions involving the Unicode datatypes are performed using the Unicode sort order. This includes mixed-mode expressions involving Unicode and non-Unicode data. For example, in the following query the varchar character constant ‘Mü’ is implicitly cast to unichar and the comparison is performed according to the Unicode sort order:

select * from authors where unicode_name > 'Mü'

The same holds true for all other comparison operators, as well as the concatenation operator “+”, the operator “in”, and the operator “between.” Once again, the goal is to retain compatibility with existing database applications.

Tables joins based on equality (equijoins) deserve special mention. These are generally optimized by the server to take advantage of indexes that defined on the participating columns. When a unichar column is joined with a char column, the latter requires a conversion, and since the character sort order and the Unicode sort order are distinct, the optimizer will ignore the index on the char column.

In SAP ASE version 12.5.1 and later, when the server’s default character set is configured to UTF-8, you can configure the server's default sort order (for char data) to be any of the above sort orders. Prior to this version, the binary sort order “bin_utf8” (ID=50) was the only well-behaved sort order for UTF-8. Although not required, the sort order for char data in UTF-8 can be selected so that it corresponds with the sort order for unichar.

There is a potential confusion regarding choice of binary sort orders for Unicode. The sort order named “binary” is the most efficient one for unichar data (UTF-16), and is thus the default. This order is based on the Unicode scalar value, meaning that all 32-bit surrogate pairs are placed after all 16-bit Unicode values. The sort order named “utf8bin” is designed to match the order of the default (most efficient) binary order for UTF-8 char data, namely “bin_utf8”. The recommended matching combinations are thus “binary” for unichar and “binary” for UTF-8 char, or “utf8bin” for unichar and “bin_utf8” for UTF-8 char. The former favors unichar efficiency, while the latter favors char efficiency. Avoid using “utf8bin” for UTF-8 char, since it is equivalent to “bin_utf8” but less efficient.