Windows Collation Name (Transact-SQL)

**APPLIES TO:** SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse Parallel 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.

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 2017

¹Unicode-only Windows collations can only be applied to column-level or expression-level data. They cannot be used as server or database collations.

²Like the Chinese (Taiwan) collation, Chinese (Macau) uses the rules of Simplified Chinese; unlike Chinese (Taiwan), it uses code page 950.

See Also

Collation and Unicode Support
ALTER TABLE (Transact-SQL)
Constants (Transact-SQL)
CREATE DATABASE (SQL Server Transact-SQL)
CREATE TABLE (Transact-SQL)
[DECLARE @local_variable (Transact-SQL)](../../t-sql/language-elements/declare-local-variable-transact-sql.md)
table (Transact-SQL)
sys.fn_helpcollations (Transact-SQL)