Skip to content

Latest commit

 

History

History
199 lines (146 loc) · 10.1 KB

collations.md

File metadata and controls

199 lines (146 loc) · 10.1 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
COLLATE (Transact-SQL)
The COLLATE statement sets the collation of a database or table column, or casts a collation to character string expression, in the SQL Database Engine.
markingmyname
maghan
wiassaf
03/03/2025
sql
t-sql
reference
COLLATE
COLLATE_TSQL
collations [SQL Server], COLLATE clause
COLLATE clause
TSQL
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

COLLATE (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricdw]

Defines a collation of a database or table column, or a collation cast operation when applied to character string expression. Collation name can be either a Windows collation name or a SQL collation name. If not specified during database creation, the database is assigned the default collation of the instance of [!INCLUDE ssNoVersion]. If not specified during table column creation, the column is assigned the default collation of the database.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

COLLATE { <collation_name> | database_default }
<collation_name> ::=
    { Windows_collation_name } | { SQL_collation_name }

Arguments

collation_name

Is the name of the collation to be applied to the expression, column definition, or database definition. collation_name can be only a specified Windows_collation_name or a SQL_collation_name. collation_name must be a literal value. collation_name can't be represented by a variable or expression.

Windows_collation_name is the collation name for a Windows Collation Name.

SQL_collation_name is the collation name for a SQL Server Collation Name.

Note

In Microsoft Fabric Data Warehouse, the COLLATE clause in a SELECT statement is unrestricted and can be applied with unsupported collations. In Microsoft Fabric Data Warehouse, all warehouses by default are configured with case-sensitive (CS) collation Latin1_General_100_BIN2_UTF8. You can also create warehouses with case-insensitive (CI) collation Latin1_General_100_CI_AS_KS_WS_SC_UTF8.

However, only supported collations can be used with the COLLATE clause in CREATE TABLE, ALTER TABLE ADD nullable column, SELECT INTO, and CREATE TABLE AS SELECT (CTAS) statements.

database_default Causes the COLLATE clause to inherit the collation of the current database.

Remarks

The COLLATE clause can be specified at several levels. These include the following:

  1. Creating or altering a database.

    You can use the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement to specify the default collation of the database. You can also specify a collation when you create a database using [!INCLUDE ssManStudioFull]. If you don't specify a collation, the database is assigned the default collation of the instance of [!INCLUDE ssNoVersion].

    [!NOTE]
    Windows Unicode-only collations can only be used with the COLLATE clause to apply collations to the nchar, nvarchar, and ntext data types on column-level and expression-level data; these can't be used with the COLLATE clause to define or change the collation of a database or server instance.

  2. Creating or altering a table column.

    You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify a collation when you create a table using [!INCLUDE ssManStudioFull]. If you don't specify a collation, the column is assigned the default collation of the database.

    You can also use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation default of the current user database for the connection instead of tempdb.

  3. Casting the collation of an expression.

    You can use the COLLATE clause to apply a character expression to a certain collation. Character literals and variables are assigned the default collation of the current database. Column references are assigned the definition collation of the column.

The collation of an identifier depends on the level at which it's defined. Identifiers of instance-level objects, such as logins and database names, are assigned the default collation of the instance. Identifiers of objects within a database, such as tables, views, and column names, are assigned the default collation of the database. For example, two tables with names different only in case might be created in a database with case-sensitive collation, but might not be created in a database with case-insensitive collation. For more information, see Database Identifiers.

Variables, GOTO labels, temporary stored procedures, and temporary tables can be created when the connection context is associated with one database, and then referenced when the context has been switched to another database. The identifiers for variables, GOTO labels, temporary stored procedures, and temporary tables are in the default collation of the server instance.

The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.

COLLATE uses collate_name to refer to the name of either the [!INCLUDE ssNoVersion] collation or the Windows collation to be applied to the expression, column definition, or database definition. collation_name can be only a specified Windows_collation_name or a SQL_collation_name and the parameter must contain a literal value. collation_name can't be represented by a variable or expression.

Collations are generally identified by a collation name, except in Setup. In Setup, you instead specify the root collation designator (the collation locale) for Windows collations, and then specify sort options that are sensitive or insensitive to case or accents.

You can execute the system function fn_helpcollations to retrieve a list of all the valid collation names for Windows collations and SQL Server collations:

SELECT name,
       description
FROM fn_helpcollations();

[!INCLUDE ssNoVersion] can support only code pages that are supported by the underlying operating system. When you perform an action that depends on collations, the [!INCLUDE ssNoVersion] collation used by the referenced object must use a code page supported by the operating system running on the computer. These actions can include the following:

  • Specifying a default collation for a database when you create or alter the database.
  • Specifying a collation for a column when you create or alter a table.
  • When restoring or attaching a database, the default collation of the database and the collation of any char, varchar, and text columns or parameters in the database must be supported by the operating system.

Note

Code page translations are supported for char and varchar data types, but not for text data type. Data loss during code page translations isn't reported.

If the collation specified or the collation used by the referenced object uses a code page not supported by Windows, [!INCLUDE ssNoVersion] displays an error.

Examples

A. Specify collation during a SELECT

The following example creates a simple table and inserts 4 rows. Then the example applies two collations when selecting data from the table, demonstrating how Chiapas is sorted differently.

CREATE TABLE Locations
(
    Place VARCHAR (15) NOT NULL
);

GO

INSERT Locations (Place)
VALUES ('Chiapas'),
('Colima'),
('Cinco Rios'),
('California');
GO
--Apply a typical collation
SELECT Place
FROM Locations
ORDER BY Place COLLATE Latin1_General_CS_AS_KS_WS ASC;
GO
-- Apply a Spanish collation
SELECT Place
FROM Locations
ORDER BY Place COLLATE Traditional_Spanish_ci_ai ASC;
GO
-- Using LIKE
SELECT Place FROM Locations
WHERE Place COLLATE Latin1_General_CI_AS LIKE 'C%' ASC;
GO

Here are the results from the first SELECT query.

Place
-------------
California
Chiapas
Cinco Rios
Colima

Here are the results from the second SELECT query.

Place
-------------
California
Cinco Rios
Colima
Chiapas

Here are the results from the third SELECT query.

Place
-------------
Chiapas
Colima
Cinco Rios
California

B. Additional examples

For additional examples that use COLLATE, see CREATE DATABASE and ALTER TABLE.

Related content