Windows Collation Name (Transact-SQL)

  • Article

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 iconTransact-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 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.

Remarks

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

Examples

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.

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)

Other Resources

Working with Collations

Help and Information

Getting SQL Server 2005 Assistance