SQL Collation Names Guide

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:
image1

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:
image2

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

3 Comments

Robert

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…

Manjot

A big thanks for this post!
I was doing a SQL 2005 install and had no idea how to find a special collation.

Leave a Reply