Windows Collation Name (Transact-SQL)

APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

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.

Topic link icon Transact-SQL Syntax Conventions

Syntax

<Windows_collation_name> :: =
CollationDesignator_<ComparisonStyle>

<ComparisonStyle> :: =
{ CaseSensitivity_AccentSensitivity [ _KanatypeSensitive ] [ _WidthSensitive ] [ _VariationSelectorSensitive ] 
}
| { _UTF8 }
| { _BIN | _BIN2 }

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 preview

  • Specifies UTF-8 enconding 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 2017.

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