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.

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

ALTER TABLE (Transact-SQL)

Constants (Transact-SQL)

CREATE DATABASE (Transact-SQL)

CREATE TABLE (Transact-SQL)

DECLARE @local\_variable (Transact-SQL)

table (Transact-SQL)

sys.fn_helpcollations (Transact-SQL)

Concepts

Collation and Unicode Support