Редактиране

Споделяне чрез

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's 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's 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%';

Related content