Windows Collation Name (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL Endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Specifies the Windows collation name in the COLLATE clause in SQL Server. The Windows collation name is composed of the collation designator and the comparison styles.
Transact-SQL syntax conventions
Syntax
<Windows_collation_name> :: =
<CollationDesignator>_<ComparisonStyle>
<ComparisonStyle> :: =
{ <CaseSensitivity>_<AccentSensitivity> [ _<KanatypeSensitive> ] [ _<WidthSensitive> ] [ _<VariationSelectorSensitive> ]
}
| { _UTF8 }
| { _BIN | _BIN2 }
Note
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Arguments
CollationDesignator
Specifies the base collation rules used by the Windows collation. The base collation rules cover the following:
- The sorting and comparison rules that are applied when dictionary sorting is specified. Sorting rules are based on alphabet or language.
- The code page used to store varchar 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
Omitting this option specifies kanatype-insensitive, KS specifies kanatype-sensitive.
WidthSensitivity
Omitting this option specifies width-insensitive, WS specifies width-sensitive.
VariationSelectorSensitivity
Applies to: Starting with SQL Server 2017 (14.x)
Omitting this option specifies variation selector-insensitive, VSS specifies variation selector-sensitive.
UTF8
Applies to: Starting with SQL Server 2019 (15.x)
Specifies UTF-8 encoding to be used for eligible data types. For more information, see Collation and Unicode Support.
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 collation, some code points may not have sort weights and/or uppercase/lowercase mappings defined. For example, compare the output of the LOWER function when it is given the same character, but in different versions of the same collation:
SELECT NCHAR(504) COLLATE Latin1_General_CI_AS AS [Uppercase],
NCHAR(505) COLLATE Latin1_General_CI_AS AS [Lowercase];
-- Ǹ ǹ
SELECT LOWER(NCHAR(504) COLLATE Latin1_General_CI_AS) AS [Version80Collation],
LOWER(NCHAR(504) COLLATE Latin1_General_100_CI_AS) AS [Version100Collation];
-- Ǹ ǹ
The first statement shows both uppercase and lowercase forms of this character in the older collation (collation does not affect the availability of characters when working with Unicode data). However, the second statement shows that an uppercase character is returned when the collation is Latin1_General_CI_AS because this code point does not have a lowercase mapping defined in that 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_CI_AS
Collation uses the Latin1 General dictionary sorting rules and maps to code page 1252. It is a version _100 collation, and is case-insensitive (CI) and accent-sensitive (AS).
Estonian_CS_AS
Collation uses the Estonian dictionary sorting rules and maps to code page 1257. It is a version _80 collation (implied by no version number in the name), and is case-sensitive (CS) and accent-sensitive (AS).
Japanese_Bushu_Kakusu_140_BIN2
Collation uses binary code point sorting rules and maps to code page 932. It is a version _140 collation, and the Japanese Bushu Kakusu 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 N'SQL%';
The following table lists all Windows collations supported in SQL Server.
| 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 |
| Bangla (Bangladesh) | Bengali_100_1 | Not available |
| Bashkir (Russia) | Bashkir_100_ | Not available |
| Basque (Basque) | Latin1_General_100_ | 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 |
| Odia (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 |
| Sakha (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 (10.0.x) or later | Hindi | Hindi |
| Deprecated, not available at server level in SQL Server 2008 (10.0.x) or later | Korean_Wansung_Unicode | Korean_Wansung_Unicode |
| Deprecated, not available at server level in SQL Server 2008 (10.0.x) or later | Lithuanian_Classic | Lithuanian_Classic |
| Deprecated, not available at server level in SQL Server 2008 (10.0.x) or later | Macedonian | Macedonian |
1 Unicode-only Windows collations can only be applied to column-level or expression-level data. They cannot be used as server or database collations.
2 Like the Chinese (Taiwan) collation, Chinese (Macao SAR) uses the rules of Simplified Chinese; unlike Chinese (Taiwan), it uses code page 950.