2 out of 6 rated this helpful - Rate this topic

Windows Collation Name (Transact-SQL)

SQL Server 2005

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

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.

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD

Windows Collation Name (Transact-SQL)

Reference

Other Resources

Help and Information

Community Additions