Windows collation name (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

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> ] [ _<VariationSelectorSensitive> ]
}
| { _UTF8 }
| { _BIN | _BIN2 }

Arguments

CollationDesignator

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

• The sorting and comparison rules that are applied when dictionary sorting is specified. Sorting rules are based on alphabet or language.
• The code page used to store varchar 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

Omitting this option specifies kanatype-insensitive, KS specifies kanatype-sensitive.

WidthSensitivity

Omitting this option specifies width-insensitive, WS specifies width-sensitive.

VariationSelectorSensitivity

Applies to: SQL Server 2017 (14.x) and later versions

Omitting this option specifies variation selector-insensitive, VSS specifies variation selector-sensitive.

UTF8

Applies to: SQL Server 2019 (15.x) and later versions

Specifies UTF-8 encoding to be used for eligible data types. For more information, see Collation and Unicode support.

{ BIN | BIN2 }

• 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 collation, some code points might not have sort weights, or uppercase/lowercase mappings defined. For example, compare the output of the LOWER function when it's given the same character, but in different versions of the same collation:

SELECT NCHAR(504) COLLATE Latin1_General_CI_AS AS [Uppercase],
       NCHAR(505) COLLATE Latin1_General_CI_AS AS [Lowercase];

Here is the result set.

The first statement shows both uppercase and lowercase forms of this character in the older collation (collation doesn't affect the availability of characters when working with Unicode data).

SELECT LOWER(NCHAR(504) COLLATE Latin1_General_CI_AS) AS [Version80Collation],
       LOWER(NCHAR(504) COLLATE Latin1_General_100_CI_AS) AS [Version100Collation];

Here is the result set.

The second statement shows that an uppercase character is returned when the collation is Latin1_General_CI_AS, because this code point doesn't have a lowercase mapping defined in that 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 table describes some examples of Windows collation names.

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 N'SQL%';

Related content

• Collation and Unicode support
• ALTER TABLE (Transact-SQL)
• Constants (Transact-SQL)
• CREATE DATABASE
• CREATE TABLE (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• Table (Transact-SQL)
• sys.fn_helpcollations