INF: How to Create a Custom Sort Order Definition File

• Microsoft SQL Server versions 7.0, 6.5

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 <Sql_root>\charsets\<Code_page> 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:

1. 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.



2. 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.

Additional query words: char set charset charsets sets sortorder

Additional query words:

Keywords : SSrvGen SSrvInst
Version : winnt:6.5,7.0
Platform : winnt
Issue type : kbinfo