Windows Collation Name (Transact-SQL)
Specifies the Windows collation name in the COLLATE clause in SQL Server 2012. The Windows collation name is composed of the collation designator and the comparison styles.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
<Windows_collation_name> :: =
CollationDesignator_<ComparisonStyle>
<ComparisonStyle> :: =
{ CaseSensitivity_AccentSensitivity
[ _KanatypeSensitive ] [ _WidthSensitive ] }
| { _BIN | _BIN2 }
Arguments
CollationDesignator
Specifies the base collation rules used by the Windows collation. The base collation rules cover the following:The sorting rules that are applied when dictionary sorting is specified. Sorting rules are based on alphabet or language.
The code page used to store non-Unicode character data.
Some examples are:
Latin1_General or French: both use code page 1252.
Turkish: uses code page 1254.
CaseSensitivity
CI specifies case-insensitive, CS specifies case-sensitive.AccentSensitivity
AI specifies accent-insensitive, AS specifies accent-sensitive.KanatypeSensitive
Omitted specifies kanatype-insensitive, KS specifies kanatype-sensitive.WidthSensitivity
Omitted specifies width-insensitive, WS specifies width-sensitive.BIN
Specifies the backward-compatible binary sort order to be used.BIN2
Specifies the binary sort order that uses code-point comparison semantics.
Remarks
Depending on the version of the collations some code points may be undefined. For example compare:
SELECT LOWER(nchar(504) COLLATE Latin1_General_CI_AS);
SELECT LOWER (nchar(504) COLLATE Latin1_General_100_CI_AS);
GO
The first line returns an uppercase character when the collation is Latin1_General_CI_AS, because this code point is undefined in this collation.
When working with some languages, it can be critical to avoid the older collations. For example, this is true for Telegu.
In some cases Windows collations and SQL Server collations can generate different query plans for the same query.
Examples
The following are some examples of Windows collation names:
- Latin1_General_100_
Collation uses the Latin1 General dictionary sorting rules, code page 1252. Is case-insensitive and accent-sensitive. Collation uses the Latin1 General dictionary sorting rules and maps to code page 1252. Shows the version number of the collation if it is a Windows collation: _90 or _100. Is case-insensitive (CI), and accent-sensitive (AS).
Estonian_CS_AS
Collation uses the Estonian dictionary sorting rules, code page 1257. Is case-sensitive and accent-sensitive.
Latin1_General_BIN
Collation uses code page 1252 and binary sorting rules. The Latin1 General dictionary sorting rules are ignored.
Windows Collations
To list the Windows collations supported by your instance of SQL Server, execute the following query.
SELECT * FROM sys.fn_helpcollations() WHERE name NOT LIKE 'SQL%';
The following table lists all Windows collations supported in SQL Server 2012.
Windows locale |
Collation Version 100 |
Collation Version 90 |
|---|---|---|
Alsatian (France) |
Latin1_General_100_ |
Not available |
Amharic (Ethiopia) |
Latin1_General_100_ |
Not available |
Armenian (Armenia) |
Cyrillic_General_100_ |
Not available |
Assamese (India) |
Assamese_100_ 1 |
Not available |
Bashkir (Russia) |
Bashkir_100_ |
Not available |
Basque (Basque) |
Latin1_General_100_ |
Not available |
Bengali (Bangladesh) |
Bengali_100_1 |
Not available |
Bengali (India) |
Bengali_100_1 |
Not available |
Bosnian (Bosnia and Herzegovina, Cyrillic) |
Bosnian_Cyrillic_100_ |
Not available |
Bosnian (Bosnia and Herzegovina, Latin) |
Bosnian_Latin_100_ |
Not available |
Breton (France) |
Breton_100_ |
Not available |
Chinese (Macao SAR) |
Chinese_Traditional_Pinyin_100_ |
Not available |
Chinese (Macao SAR) |
Chinese_Traditional_Stroke_Order_100_ |
Not available |
Chinese (Singapore) |
Chinese_Simplified_Stroke_Order_100_ |
Not available |
Corsican (France) |
Corsican_100_ |
Not available |
Croatian (Bosnia and Herzegovina, Latin) |
Croatian_100_ |
Not available |
Dari (Afghanistan) |
Dari_100_ |
Not available |
English (India) |
Latin1_General_100_ |
Not available |
English (Malaysia) |
Latin1_General_100_ |
Not available |
English (Singapore) |
Latin1_General_100_ |
Not available |
Filipino (Philippines) |
Latin1_General_100_ |
Not available |
Frisian (Netherlands) |
Frisian_100_ |
Not available |
Georgian (Georgia) |
Cyrillic_General_100_ |
Not available |
Greenlandic (Greenland) |
Danish_Greenlandic_100_ |
Not available |
Gujarati (India) |
Indic_General_100_1 |
Indic_General_90_ |
Hausa (Nigeria, Latin) |
Latin1_General_100_ |
Not available |
Hindi (India) |
Indic_General_100_1 |
Indic_General_90_ |
Igbo (Nigeria) |
Latin1_General_100_ |
Not available |
Inuktitut (Canada, Latin) |
Latin1_General_100_ |
Not available |
Inuktitut (Syllabics) Canada |
Latin1_General_100_ |
Not available |
Irish (Ireland) |
Latin1_General_100_ |
Not available |
Japanese (Japan XJIS) |
Japanese_XJIS_100_ |
Japanese_90_, Japanese_ |
Japanese (Japan) |
Japanese_Bushu_Kakusu_100_ |
Not available |
Kannada (India) |
Indic_General_100_1 |
Indic_General_90_ |
Khmer (Cambodia) |
Khmer_100_1 |
Not available |
K'iche (Guatemala) |
Modern_Spanish_100_ |
Not available |
Kinyarwanda (Rwanda) |
Latin1_General_100_ |
Not available |
Konkani (India) |
Indic_General_100_1 |
Indic_General_90_ |
Lao (Lao PDR) |
Lao_100_1 |
Not available |
Lower Sorbian (Germany) |
Latin1_General_100_ |
Not available |
Luxembourgish (Luxembourg) |
Latin1_General_100_ |
Not available |
Malayalam (India) |
Indic_General_100_1 |
Not available |
Maltese (Malta) |
Maltese_100_ |
Not available |
Maori (New Zealand) |
Maori_100_ |
Not available |
Mapudungun (Chile) |
Mapudungan_100_ |
Not available |
Marathi (India) |
Indic_General_100_1 |
Indic_General_90_ |
Mohawk (Canada) |
Mohawk_100_ |
Not available |
Mongolian (PRC) |
Cyrillic_General_100_ |
Not available |
Nepali (Nepal) |
Nepali_100_1 |
Not available |
Norwegian (Bokmål, Norway) |
Norwegian_100_ |
Not available |
Norwegian (Nynorsk, Norway) |
Norwegian_100_ |
Not available |
Occitan (France) |
French_100_ |
Not available |
Oriya (India) |
Indic_General_100_1 |
Not available |
Pashto (Afghanistan) |
Pashto_100_1 |
Not available |
Persian (Iran) |
Persian_100_ |
Not available |
Punjabi (India) |
Indic_General_100_1 |
Indic_General_90_ |
Quechua (Bolivia) |
Latin1_General_100_ |
Not available |
Quechua (Ecuador) |
Latin1_General_100_ |
Not available |
Quechua (Peru) |
Latin1_General_100_ |
Not available |
Romansh (Switzerland) |
Romansh_100_ |
Not available |
Sami (Inari, Finland) |
Sami_Sweden_Finland_100_ |
Not available |
Sami (Lule,Norway) |
Sami_Norway_100_ |
Not available |
Sami (Lule, Sweden) |
Sami_Sweden_Finland_100_ |
Not available |
Sami (Northern, Finland) |
Sami_Sweden_Finland_100_ |
Not available |
Sami (Northern,Norway) |
Sami_Norway_100_ |
Not available |
Sami (Northern, Sweden) |
Sami_Sweden_Finland_100_ |
Not available |
Sami (Skolt, Finland) |
Sami_Sweden_Finland_100_ |
Not available |
Sami (Southern, Norway) |
Sami_Norway_100_ |
Not available |
Sami (Southern, Sweden) |
Sami_Sweden_Finland_100_ |
Not available |
Sanskrit (India) |
Indic_General_100_1 |
Indic_General_90_ |
Serbian (Bosnia and Herzegovina, Cyrillic) |
Serbian_Cyrillic_100_ |
Not available |
Serbian (Bosnia and Herzegovina, Latin) |
Serbian_Latin_100_ |
Not available |
Serbian (Serbia, Cyrillic) |
Serbian_Cyrillic_100_ |
Not available |
Serbian (Serbia, Latin) |
Serbian_Latin_100_ |
Not available |
Sesotho sa Leboa/Northern Sotho (South Africa) |
Latin1_General_100_ |
Not available |
Setswana/Tswana (South Africa) |
Latin1_General_100_ |
Not available |
Sinhala (Sri Lanka) |
Indic_General_100_1 |
Not available |
Swahili (Kenya) |
Latin1_General_100_ |
Not available |
Syriac (Syria) |
Syriac_100_1 |
Syriac_90_ |
Tajik (Tajikistan) |
Cyrillic_General_100_ |
Not available |
Tamazight (Algeria, Latin) |
Tamazight_100_ |
Not available |
Tamil (India) |
Indic_General_100_1 |
Indic_General_90_ |
Telugu (India) |
Indic_General_100_1 |
Indic_General_90_ |
Tibetan (PRC) |
Tibetan_100_1 |
Not available |
Turkmen (Turkmenistan) |
Turkmen_100_ |
Not available |
Uighur (PRC) |
Uighur_100_ |
Not available |
Upper Sorbian (Germany) |
Upper_Sorbian_100_ |
Not available |
Urdu (Pakistan) |
Urdu_100_ |
Not available |
Welsh (United Kingdom) |
Welsh_100_ |
Not available |
Wolof (Senegal) |
French_100_ |
Not available |
Xhosa/isiXhosa (South Africa) |
Latin1_General_100_ |
Not available |
Yakut (Russia) |
Yakut_100_ |
Not available |
Yi (PRC) |
Latin1_General_100_ |
Not available |
Yoruba (Nigeria) |
Latin1_General_100_ |
Not available |
Zulu/isiZulu (South Africa) |
Latin1_General_100_ |
Not available |
Deprecated, not available at server level in SQL Server 2008 or later |
Hindi |
Hindi |
Deprecated, not available at server level in SQL Server 2008 or later |
Korean_Wansung_Unicode |
Korean_Wansung_Unicode |
Deprecated, not available at server level in SQL Server 2008 or later |
Lithuanian_Classic |
Lithuanian_Classic |
Deprecated, not available at server level in SQL Server 2008 or later |
Macedonian |
Macedonian |
1Unicode-only Windows collations can only be applied to column-level or expression-level data. They cannot be used as server or database collations.
2Like the Chinese (Taiwan) collation, Chinese (Macau) uses the rules of Simplified Chinese; unlike Chinese (Taiwan), it uses code page 950.
See Also
Reference
CREATE DATABASE (Transact-SQL)
DECLARE @local\_variable (Transact-SQL)
sys.fn_helpcollations (Transact-SQL)