INF: How to Create a Custom Sort Order Definition File

ID: Q189331

The information in this article applies to:

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


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          : SSrvGen SSrvInst 
Version           : WINNT:6.5
Platform          : winnt 
Issue type        : kbhowto kbinfo

Last Reviewed: April 16, 1999