Export (0) Print
Expand All

Windows Collation Name (Transact-SQL)

Specifies the Windows collation name in the COLLATE clause. The Windows collations name is composed of the collation designator and the comparison styles.

Topic link icon Transact-SQL Syntax Conventions


<Windows_collation_name> :: = 
     CollationDesignator_<ComparisonStyle>
<ComparisonStyle> :: = 
        { CaseSensitivity_AccentSensitivity
             [ _KanatypeSensitive ] [ _WidthSensitive ]  }
  | { _BIN | _BIN2 }

CollationDesignator

Specifies the base collation rules used by the Windows collation. The base collation rules cover the following:

  • The alphabet or language whose sorting rules are applied when dictionary sorting is specified
  • 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.

For a list of collation designators, see Collation Settings in Setup.

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 is to be used.

BIN2

Specifies the binary sort order that uses code-point comparison semantics introduced in SQL Server 2005.

For a table of Windows locales with their corresponding SQL collation designators, see Collation Settings in Setup.

The following are some examples of Windows collation names:

  • Latin1_General_CI_AS
    Collation uses the Latin1 General dictionary sorting rules, code page 1252. Is case-insensitive and accent-sensitive.
  • 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.

Community Additions

ADD
Show:
© 2014 Microsoft