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 }
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
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
orFrench
: both use code page1252
.Turkish
: uses code page1254
.
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.
Column name | Result |
---|---|
Uppercase |
Ǹ |
Lowercase |
ǹ |
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.
Column name | Result |
---|---|
Version80Collation |
Ǹ |
Version100Collation |
ǹ |
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.
Collation | Description |
---|---|
Latin1_General_100_CI_AS |
Collation uses the Latin1 General dictionary sorting rules and maps to code page 1252 . It's a version _100 collation, and is case-insensitive (CI ) and accent-sensitive (AS ). |
Estonian_CS_AS |
Collation uses the Estonian dictionary sorting rules and maps to code page 1257 . It's a version _80 collation (implied by no version number in the name), and is case-sensitive (CS ) and accent-sensitive (AS ). |
Japanese_Bushu_Kakusu_140_BIN2 |
Collation uses binary code point sorting rules and maps to code page 932 . It's a version _140 collation, and the Japanese Bushu Kakusu 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 N'SQL%';