Microsoft KB Archive/189331

= INF: How to Create a Custom Sort Order Definition File =

Article ID: 189331

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q189331



SUMMARY
Appendix A of the SQL Server setup manual indicates that SQL Server can install and use a custom sort order. This article explains how to create a custom sort order definition file. After building the file, make a backup of the file and document it before installing the file. This will be useful when it becomes necessary to reinstall SQL Server or rebuild the master database and load any database from a dump.

NOTE: Using a custom sort order may have an implication on performance and query results, so use it sparingly.



MORE INFORMATION
SQL Server sorts based on the sort order definition file. SQL Server setup provides the option to select a sort order, and then installs the information from the relevant definition file. All the definition files that come with SQL Server are kept in the \charsets\ directory. You can create a custom sort order definition file and install it for sorting and comparisons.

Except for a binary sort order, the order in which characters are kept in the definition file is the order in which SQL Server performs the sort. For each binary sort order, characters are sorted based on their ASCII value, and in the definition file a flag is set to indicate that the binary sort order should be used (binary = "true"). You start by making a copy of any definition file that comes with SQL Server (the closest one to match to your needs) and modifying it to create a custom definition file. The following are some steps to follow when creating a custom sort order definition file:   Select a unique 'ID'. This is saved in syscharsets (as ID) and sysconfigures (as the value column for config 1123). The following ID values are used in the set of charsets and sortorders included with SQL Server 6.5:

      ID      ID      hex     dec   CharSet --

0x01    1    CharSet iso_1 0x02    2    CharSet cp850 0x03    3    CharSet cp437 0x04    4    CharSet cp1250 0x05    5    CharSet cp1251 0x06    6    CharSet cp1253 0x07    7    CharSet cp1254 0x08    8    CharSet cp1255 0x09    9    CharSet cp1256 0x0A   10    CharSet cp1257 0x0C   12    CharSet cp949 0x0D   13    CharSet cp950 0x0E   14    CharSet cp936 0x6D  109    CharSet cp932 (duplicated in list below)

ID     ID   Sort order hex    dec   filename --

0x1F   31    diction.437 0x20   32    nocase.437 0x21   33    nocasepr.437 0x22   34    noaccent.437 0x29   41    diction.850 0x2A   42    nocase.850 0x2B   43    nocasepr.850 0x2C   44    noaccent.850 0x33   51    diction.iso 0x34   52    nocase.iso 0x35   53    nocasepr.iso 0x36   54    noaccent.iso 0x37   55    altdict.850 0x38   56    altnocsp.850 0x39   57    altnoacc.850 0x3A   58    scannocp.850 0x3B   59    scandict.850 0x3C   60    scannocs.850 0x3D   61    altnocs.850 0x50   80    binary.250 0x51   81    diction.250 0x52   82    nocase.250 0x53   83    csydic.250 0x54   84    csync.250 0x55   85    hundic.250 0x56   86    hunnc.250 0x57   87    plkdic.250 0x58   88    plknc.250 0x59   89    romdic.250 0x5A   90    romnc.250 0x5B   91    shldic.250 0x5C   92    shlnc.250 0x5D   93    skydic.250 0x5E   94    skync.250 0x5F   95    slvdic.250 0x60   96    slvnc.250 0x68  104    binary.251 0x69  105    diction.251 0x6A  106    nocase.251 0x6B  107    ukrdic.251 0x6C  108    ukrnc.251 0x6D  109    CharSet cp932 0x70  112    binary.253 0x71  113    diction.253 0x72  114    nocase.253 0x78  120    grmxtdct.253 0x79  121    graltdct.253 0x7C  124    grnoacce.253 0x80  128    binary.254 0x81  129    diction.254 0x82  130    nocase.254 0x88  136    binary.255 0x89  137    diction.255 0x8A  138    nocase.255 0x90  144    binary.256 0x91  145    diction.256 0x92  146    nocase.256 0x98  152    binary.257 0x99  153    diction.257 0x9A  154    nocase.257 0x9B  155    etidic.257 0x9C  156    etinc.257 0x9D  157    lvidic.257 0x9E  158    lvinc.257 0x9F  159    lthdic.257 0xA0  160    lthnc.257 0xB7  183    danonocp.iso 0xB8  184    svf1nocp.iso 0xB9  185    svf2nocp.iso 0xBA  186    islanocp.iso 0x32   50    bin_cp932 0xC1  193    nls_cp932 0xC2  194    bin_cp949 0xC3  195    nls_cp949 0xC4  196    bin_cp950 0xC5  197    nls_cp950 0xC6  198    bin_cp936 0xC7  199    nls_cp936

Based on these numbers, you should choose an ID higher than 200.

NOTE: These sort order IDs are subject to change in subsequent versions of SQL Server. A custom sort order ID should be checked against new system values before upgrading to a new version of SQL Server.  Select 'charset' to indicate the character set that you are using. For example, if you are creating a new sort order for the ISO 8859-1 character set, set the 'charset' to be iso_1. This is translated to an ID and is saved as csid for the particular sort order ID in syscharsets. The 'name' and 'description' are also saved in syscharsets.

For more information about how to install a custom sort order, refer to the SQL Server Books Online.

Additional query words: char set charset charsets sets sortorder

Keywords: kbinfo KB189331

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.