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