Pre SQL Server 2008, matching a SQL collation to an existing build could be a bit of a challenge. Typically you would have to research which long SQL collation name would give you the short SQL collation name:
As shown below, in SQL Server 2008 onwards you now get presented the short name with the associated long name to make the process simpler:
The following table made up from here and here, may help you pick the correct one; or you could try one of these queries:
SELECT * FROM master.sys.fn_helpcollations() WHERE name LIKE 'SQL%'
SELECT * FROM ::fn_helpcollations() WHERE name LIKE 'SQL%'
ID | Short Name | Long Name | |
---|---|---|---|
30 | SQL_Latin1_General_Cp437_BIN | Binary order, for use with the 437 (U.S. English) character set. | |
31 | SQL_Latin1_General_Cp437_CS_AS | Dictionary order, case-sensitive, for use with the 437 (U.S. English) character set. | |
32 | SQL_Latin1_General_Cp437_CI_AS | Dictionary order, case-insensitive, for use with the 437 (U.S. English) character set. | |
33 | SQL_Latin1_General_Pref_CP437_CI_AS | Dictionary order, case-insensitive, uppercase preference, for use with the 437 (U.S. English) character set. | |
34 | SQL_Latin1_General_Cp437_CI_AI | Dictionary order, case-insensitive, accent-insensitive, for use with the 437 (U.S. English) character set. | |
40 | SQL_Latin1_General_Cp850_BIN | Binary order, for use with the 850 (Multilingual) character set. | |
41 | SQL_Latin1_General_Cp850_CS_AS | Dictionary order, case-sensitive, for use with the 850 (Multilingual) character set. | |
42 | SQL_Latin1_General_Cp850_CI_AS | Dictionary order, case-insensitive, for use with the 850 (Multilingual) character set. | |
43 | SQL_Latin1_General_Pref_CP850_CI_AS | Dictionary order, case-insensitive, uppercase preference, for use with the 850 (Multilingual) character set. | |
44 | SQL_Latin1_General_Cp850_CI_AI | Dictionary order, case-insensitive, accent-insensitive, for use with the 850 (Multilingual) character set. | |
49 | SQL_1Xcompat_CP850_CI_AS | Strict compatibility with version 1.x case-insensitive databases, for use with the 850 (Multilingual) character set. | |
51 | SQL_Latin1_General_Cp1_CS_AS | Dictionary order, case-sensitive, for use with 1252 character set. | |
52 | SQL_Latin1_General_Cp1_CI_AS | Dictionary order, case-insensitive, for use with 1252 character set. | |
53 | SQL_Latin1_General_Pref_CP1_CI_AS | Dictionary order, case-insensitive, uppercase preference, for use with 1252 character set. | |
54 | SQL_Latin1_General_Cp1_CI_AI | Dictionary order, case-insensitive, accent-insensitive, for use with 1252 character set. | |
55 | SQL_AltDiction_Cp850_CS_AS | Alternate dictionary order, case-sensitive, for use with the 850 (Multilingual) character set. | |
56 | SQL_AltDiction_Pref_CP850_CI_AS | Alternate dictionary order, case-insensitive, uppercase preference, for use with the 850 (Multilingual) character set. | |
57 | SQL_AltDiction_Cp850_CI_AI | Alternate dictionary order, case-insensitive, accent-insensitive, for use with the 850 (Multilingual) character set. | |
58 | SQL_Scandinavian_Pref_Cp850_CI_AS | Scandinavian dictionary order, case-insensitive, uppercase preference, for use with the 850 (Multilingual) character set. | |
59 | SQL_Scandinavian_Cp850_CS_AS | Scandinavian dictionary order, case-sensitive, for use with the 850 (Multilingual) character set. | |
60 | SQL_Scandinavian_Cp850_CI_AS | Scandinavian dictionary order, case-insensitive, for use with the 850 (Multilingual) character set. | |
61 | SQL_AltDiction_Cp850_CI_AS | Alternate dictionary order, case-insensitive, for use with the 850 (Multilingual) character set. | |
81 | SQL_Latin1_General_Cp1250_CS_AS | Dictionary order, case-sensitive, for use with the 1250 (Central European) character set. | |
82 | SQL_Latin1_General_Cp1250_CI_AS | Dictionary order, case-insensitive, for use with the 1250 (Central European) character set. | |
83 | SQL_Czech_Cp1250_CS_AS | Czech dictionary order, case-sensitive, for use with the 1250 (Central European) character set. | |
84 | SQL_Czech_Cp1250_CI_AS | Czech dictionary order, case-insensitive, for use with the 1250 (Central European) character set. | |
85 | SQL_Hungarian_Cp1250_CS_AS | Hungarian dictionary order, case-sensitive, for use with the 1250 (Central European) character set. | |
86 | SQL_Hungarian_Cp1250_CI_AS | Hungarian dictionary order, case-insensitive, for use with the 1250 (Central European) character set. | |
87 | SQL_Polish_Cp1250_CS_AS | Polish dictionary order, case-sensitive, for use with the 1250 (Central European) character set. | |
88 | SQL_Polish_Cp1250_CI_AS | Polish dictionary order, case-insensitive, for use with the 1250 (Central European) character set. | |
89 | SQL_Romanian_Cp1250_CS_AS | Romanian dictionary order, case-sensitive, for use with the 1250 (Central European) character set. | |
90 | SQL_Romanian_Cp1250_CI_AS | Romanian dictionary order, case-insensitive, for use with the 1250 (Central European) character set. | |
91 | SQL_Croatian_Cp1250_CS_AS | Croatian dictionary order, case-sensitive, for use with the 1250 (Central European) character set. | |
92 | SQL_Croatian_Cp1250_CI_AS | Croatian dictionary order, case-insensitive, for use with the 1250 (Central European) character set. | |
93 | SQL_Slovak_Cp1250_CS_AS | Slovak dictionary order, case-sensitive, for use with the 1250 (Central European) character set. | |
94 | SQL_Slovak_Cp1250_CI_AS | Slovak dictionary order, case-insensitive, for use with the 1250 (Central European) character set. | |
95 | SQL_Slovenian_Cp1250_CS_AS | Slovenian dictionary order, case-sensitive, for use with the 1250 (Central European) character set. | |
96 | SQL_Slovenian_Cp1250_CI_AS | Slovenian dictionary order, case-insensitive, for use with the 1250 (Central European) character set. | |
105 | SQL_Latin1_General_Cp1251_CS_AS | Dictionary order, case-sensitive, for use with the 1251 (Cyrillic) character set. | |
106 | SQL_Latin1_General_Cp1251_CI_AS | Dictionary order, case-insensitive, for use with the 1251 (Cyrillic) character set. | |
107 | SQL_Ukrainian_Cp1251_CS_AS | Ukrainian dictionary order, case-sensitive, for use with the 1251 (Cyrillic) character set. | |
108 | SQL_Ukrainian_Cp1251_CI_AS | Ukrainian dictionary order, case-insensitive, for use with the 1251 (Cyrillic) character set. | |
113 | SQL_Latin1_General_Cp1253_CS_AS | Dictionary order, case-sensitive, for use with the 1253 (Greek) character set. | |
114 | SQL_Latin1_General_Cp1253_CI_AS | Dictionary order, case-insensitive, for use with the 1253 (Greek) character set. | |
120 | SQL_MixDiction_Cp1253_CS_AS | Mixed dictionary order, for use with the 1253 (Greek) character set. | |
121 | SQL_AltDiction_Cp1253_CS_AS | Dictionary order, case-sensitive, accent-sensitive, for use with the 1253 (Greek) character set. | |
124 | SQL_Latin1_General_Cp1253_CI_AI | Dictionary order, case-insensitive, accent-insensitive, for use with the 1253 (Greek) character set. | |
129 | SQL_Latin1_General_Cp1254_CS_AS | Dictionary order, case-sensitive, for use with the 1254 (Turkish) character set. | |
130 | SQL_Latin1_General_Cp1254_CI_AS | Dictionary order, case-insensitive, for use with the 1254 (Turkish) character set. | |
137 | SQL_Latin1_General_Cp1255_CS_AS | Dictionary order, case-sensitive, for use with the 1255 (Hebrew) character set. | |
138 | SQL_Latin1_General_Cp1255_CI_AS | Dictionary order, case-insensitive, for use with the 1255 (Hebrew) character set. | |
145 | SQL_Latin1_General_Cp1256_CS_AS | Dictionary order, case-sensitive, for use with the 1256 (Arabic) character set. | |
146 | SQL_Latin1_General_Cp1256_CI_AS | Dictionary order, case-insensitive, for use with the 1256 (Arabic) character set. | |
153 | SQL_Latin1_General_Cp1257_CS_AS | Dictionary order, case-sensitive, for use with the 1257 (Baltic) character set. | |
154 | SQL_Latin1_General_Cp1257_CI_AS | Dictionary order, case-insensitive, for use with the 1257 (Baltic) character set. | |
155 | SQL_Estonian_Cp1257_CS_AS | Estonian dictionary order, case-sensitive, for use with the 1257 (Baltic) character set. | |
156 | SQL_Estonian_Cp1257_CI_AS | Estonian dictionary order, case-insensitive, for use with the 1257 (Baltic) character set. | |
157 | SQL_Latvian_Cp1257_CS_AS | Latvian dictionary order, case-sensitive, for use with the 1257 (Baltic) character set. | |
158 | SQL_Latvian_Cp1257_CI_AS | Latvian dictionary order, case-insensitive, for use with the 1257 (Baltic) character set. | |
159 | SQL_Lithuanian_Cp1257_CS_AS | Lithuanian dictionary order, case-sensitive, for use with the 1257 (Baltic) character set. | |
160 | SQL_Lithuanian_Cp1257_CI_AS | Lithuanian dictionary order, case-insensitive, for use with the 1257 (Baltic) character set. | |
183 | SQL_Danish_Pref_Cp1_CI_AS | Danish/Norwegian dictionary order, case-insensitive, uppercase preference, for use with 1252 character set. | |
184 | SQL_SwedishPhone_Pref_Cp1_CI_AS | Finnish-Swedish (Phone), case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 184 on Code Page 1252 for non-Unicode Data. | |
185 | SQL_SwedishStd_Pref_Cp1_CI_AS | Finnish-Swedish (Standard), case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 185 on Code Page 1252 for non-Unicode Data. | |
186 | SQL_Icelandic_Pref_Cp1_CI_AS | Icelandic dictionary order, case-insensitive, uppercase preference, for use with 1252 character set. | |
210 | SQL_EBCDIC037_CP1_CS_AS | Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 210 on Code Page 1252 for non-Unicode Data | |
211 | SQL_EBCDIC273_CP1_CS_AS | German-PhoneBook, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 211 on Code Page 1252 for non-Unicode Data | |
212 | SQL_EBCDIC277_CP1_CS_AS | Danish-Norwegian, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 212 on Code Page 1252 for non-Unicode Data | |
213 | SQL_EBCDIC278_CP1_CS_AS | Finnish-Swedish, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 213 on Code Page 1252 for non-Unicode Data | |
214 | SQL_EBCDIC280_CP1_CS_AS | Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 214 on Code Page 1252 for non-Unicode Data | |
215 | SQL_EBCDIC284_CP1_CS_AS | Modern-Spanish, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 215 on Code Page 1252 for non-Unicode Data | |
216 | SQL_EBCDIC285_CP1_CS_AS | Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 216 on Code Page 1252 for non-Unicode Data | |
217 | SQL_EBCDIC297_CP1_CS_AS | French, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 217 on Code Page 1252 for non-Unicode Data |
At least one guy on the whole Internet provided clear tmapping of Collation abbreviation name to Description. Very critical when you install SQL 2005.
Thanks a lot!
And Booo to Microsoft….they don’t have this mapping anywhere…
A big thanks for this post!
I was doing a SQL 2005 install and had no idea how to find a special collation.
Thanks Manjot, I’m glad it helped :)