Collat​​e SQL命令概述

In this article, we will review the Collate SQL command. First, let us see what collation in the SQL Server is.

在本文中，我们将回顾Collat​​e SQL命令。 首先，让我们看看SQL Server中的排序规则是什么。

Collation is a set of rules that tell database engine how to compare and sort the character data in SQL Server.

排序规则是一组规则，它们告诉数据库引擎如何比较和排序SQL Server中的字符数据。

Collation can be set at different levels in SQL Server. Below are the three levels:

可以在SQL Server中的不同级别上设置排序规则。 以下是三个级别：

• SQL Server level
  SQL Server级别
• Database level
  数据库级
• Column level
  列级

SQL Server级别 (SQL Server level)

Collation setting at SQL Server level can be specified while installing SQL Server. Please refer to the below image:

在安装SQL Server时，可以指定SQL Server级别的排序规则设置。 请参考下图：

The collation of the SQL Server is set to SQL_Latin1_General_CP1_CI_AS.

SQL Server的排序规则设置为SQL_Latin1_General_CP1_CI_AS。

Break down of the collation setting is as below:

排序规则设置的细分如下：

• SQL – All SQL Server collations will have the prefix SQL SQL –所有SQL Server排序规则都将带有前缀SQL
• Latin1_General – represents the sort rule, Latin1_General –表示排序规则， CI means case insensitive and CI表示不区分大小写， AS means accent sensitive AS表示区分重音

Execute the following T-SQL script to know the collation of the SQL Server instance:

执行以下T-SQL脚本以了解SQL Server实例的排序规则：

SELECT SERVERPROPERTY('collation')

You can also use the stored procedure sp_helpsort to know the collation of the SQL server instance.

您还可以使用存储过程sp_helpsort来了解SQL Server实例的排序规则。

数据库级 (Database level)

By default, all system and user databases will inherit the collation setting that was specified at the SQL Server level during installation. The below image shows the default collation inherited from the server level when collation is not specified explicitly while creating a database. Here the collation of SQL Server instance is SQL_Latin1_General_CP1_CI_AS which is the same as the database:

默认情况下，所有系统数据库和用户数据库都将继承安装过程中在SQL Server级别上指定的排序规则设置。 下图显示了在创建数据库时未明确指定排序规则时从服务器级别继承的默认排序规则。 在这里，SQL Server实例的排序规则是SQL_Latin1_General_CP1_CI_AS，与数据库相同：

CREATE DATABASE [Database_DefaultCollation]
GO

We can also specify the collation of a database while creating the database using the Collate SQL command. Use the below T-SQL script which creates the database with collation SQL_Latin1_General_CP1_CS_AS:

我们还可以在使用Collat​​e SQL命令创建数据库时指定数据库的排序规则。 使用下面的T-SQL脚本，该脚本使用排序规则SQL_Latin1_General_CP1_CS_AS创建数据库：

CREATE DATABASE [Database_WithCollation]
 COLLATE SQL_Latin1_General_CP1_CS_AS
GO

Please refer to the below image that shows the database is created with the collation specified using the Collate SQL command:

请参考下图，该图显示了使用Collat​​e SQL命令指定的排序规则创建的数据库：

You can also modify the database collation after creating the database. In this case, use the below T-SQL scripts which modifies the collation setting of a database:

您还可以在创建数据库后修改数据库排序规则。 在这种情况下，请使用以下T-SQL脚本修改数据库的排序规则设置：

USE [master]
GO
ALTER DATABASE [Database_WithCollation] COLLATE SQL_Latin1_General_CP1_CI_AS
GO

Execute the following T-SQL script to know the collation of the database. In this case, the “Sample” is the name of the database. Replace it with yours:

执行以下T-SQL脚本以了解数据库的排序规则。 在这种情况下，“样本” 是数据库的名称。 用您的替换它：

SELECT DATABASEPROPERTYEX('Sample','collation');

列级 (Column level)

By default, a new character type column inherits the collation of the database unless you specify the collation explicitly while creating the table.

默认情况下，除非您在创建表时显式指定排序规则，否则新的字符类型列将继承数据库的排序规则。

To create a column with a different collation you can specify collation using Collate SQL command. Please refer to the below T-SQL script which creates a column with the specified collation:

要创建具有不同排序规则的列，可以使用Collat​​e SQL命令指定排序规则。 请参考下面的T-SQL脚本，该脚本创建具有指定排序规则的列：

CREATE TABLE Products (ProductID int, ProductName varchar(50) COLLATE SQL_Latin1_General_CP437_BIN)

Collation setting can be specified on the below character column types:

可以在以下字符列类型上指定排序规则设置：

• varchar
  varchar
• nvarchar
  nvarchar
• char
  烧焦
• nchar
  nchar
• text
  文本
• ntext
  文字

To view the collation of a column on the table, execute the following stored procedure with the table name as a parameter:

要查看表上列的排序规则，请以表名称为参数执行以下存储过程：

sp_help Product

例子 (Examples)

Let us create a table and insert sample data for the demo purpose:

让我们创建一个表并插入示例数据以进行演示：

create table Product (ProductID int, ProductName varchar(50))
GO
 
INSERT INTO Product values (1,'Book'), (2,'book'), (3,'ᴃook')
GO

Now let us query the Product table. Please refer to the below image it returns two rows and ignores case as the collation is case insensitive. i.e. SQL_Latin1_General_CP1_CI_AS:

现在让我们查询产品表。 请参考下图，它返回两行并忽略大小写，因为排序规则不区分大小写。 即SQL_Latin1_General_CP1_CI_AS：

Let’s explicitly specify the collation using the Collate SQL command in the Select statement:

让我们使用Select语句中的Collat​​e SQL命令显式指定排序规则：

SELECT * FROM Product WHERE ProductName COLLATE SQL_Latin1_General_CP1_CS_AS ='Book'

Check out the below image that shows only one row in the result set as we have specified the collation in the select statement which is case sensitive:

请查看下面的图片，该图片仅显示结果集中的一行，因为我们在select语句中指定了排序规则，区分大小写：

Let’s execute the following query that returns all the three records as the collation specified is case insensitive and accent insensitive:

让我们执行以下查询，返回所有三个记录，因为指定的排序规则不区分大小写和不区分重音：

SELECT * FROM Product WHERE ProductName COLLATE SQL_Latin1_General_CP850_CI_AI ='book'

连接不同排序规则的两列 (Joining the two columns of a different collation)

For the demo purpose, I am creating two tables Products and ProductDesc with different collation on the column Pcode. Use the below T-SQL script:

出于演示目的，我将在Pcode列上创建两个具有不同排序规则的表Products和ProductDesc。 使用下面的T-SQL脚本：

create table Products (Pcode varchar(10), Pname varchar(50) )
 
INSERT INTO Products VALUES ('BK', 'Book'),('PN','Pen')
 
 
 
CREATE TABLE ProductDesc (Pcode varchar(10) collate SQL_Latin1_General_CP850_CI_AI , Pdesc varchar(100))
 
 
INSERT INTO ProductDesc values ('BK','TEST'),('PN','TEST2')

Let’s join these two tables on the column Pcode. It throws an error:

让我们在Pcode列上连接这两个表。 它抛出一个错误：

Msg 468, Level 16, State 9, Line 22
Cannot resolve the collation conflict between “SQL_Latin1_General_CP850_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

消息468，第16层，状态9，第22行
等于操作无法解决“ SQL_Latin1_General_CP850_CI_AI”和“ SQL_Latin1_General_CP1_CI_AS”之间的排序规则冲突。

This is because the collation of the column Pcode in both tables are different:

这是因为两个表中列Pcode的排序规则不同：

SELECT * FROM Products P INNER JOIN 
ProductDesc D ON P.Pcode=D.Pcode

To make the join work, you must use the Collate SQL command in the Select statement as shown in the below T-SQL script:

要使联接生效，必须在Select语句中使用Collat​​e SQL命令，如以下T-SQL脚本所示：

SELECT * FROM Products P INNER JOIN 
ProductDesc D ON P.Pcode=D.Pcode collate SQL_Latin1_General_CP1_CI_AS

Tempdb整理 (Tempdb collation)

The collation of the tempdb will be the same as the collation of the SQL Server instance. In some cases, we may use different collations at the database level and server level. In this case, we may run to issues if we use temporary tables and making joins with the tables in the database.

tempdb的排序规则将与SQL Server实例的排序规则相同。 在某些情况下，我们可能在数据库级别和服务器级别使用不同的排序规则。 在这种情况下，如果我们使用临时表并与数据库中的表进行联接，则可能会遇到问题。

For example, the collation of SQL Server instance is SQL_Latin1_General_CP1_CI_AS and I am creating a database with different collation, i.e. SQL_Latin1_General_CP437_BIN. Now create a table with a column of type varchar which will inherit the database collation SQL_Latin1_General_CP437_BIN:

例如，SQL Server实例的排序规则是SQL_Latin1_General_CP1_CI_AS，并且我正在创建具有不同排序规则的数据库，即SQL_Latin1_General_CP437_BIN 。 现在创建一个具有varchar类型的列的表，该表将继承数据库排序规则SQL_Latin1_General_CP437_BIN：

CREATE DATABASE [SampleDB]
 COLLATE SQL_Latin1_General_CP437_BIN
GO
 
Use SampleDB
GO
create table Product (Pcode varchar(5), Pname varchar(30))
GO
 
INSERT INTO Product values ('BK','Book')

Now create a temp table with a column of type varchar which will inherit the collation of tempdb database i.e. SQL_Latin1_General_CP1_CI_AS:

现在创建一个带有varchar类型的列的临时表，该表将继承tempdb数据库的排序规则，即SQL_Latin1_General_CP1_CI_AS：

create table #Product (Pcode varchar(5), Pname varchar(30))
GO
 
 
INSERT INTO #Product values ('BK','Book')

Now joining these tables on column Pcode will throw an error as the collations are different:

现在，由于排序规则不同，将这些表连接到Pcode列上将引发错误：

In these cases, we must explicitly specify the collation using Collate SQL command while creating a temp table in stored procedures or T-SQL batch.

在这些情况下，我们必须在存储过程或T-SQL批处理中创建临时表时使用Collat​​e SQL命令明确指定排序规则。

It is better to use the same collation at the database level and SQL Server level in order to avoid such issues while performing operations on temp tables or cross-database operations when the two databases have a different collation.

最好在数据库级别和SQL Server级别使用相同的排序规则，以避免在两个数据库具有不同排序规则的临时表或跨数据库操作上执行操作时避免此类问题。

结论 (Conclusion)

In this article, we explored the collation setting in SQL Server at different levels and querying tables using the Collate SQL command. In case you have any questions, feel free to ask in the comment section below.

在本文中，我们探索了SQL Server中不同级别的排序规则设置，并使用Collat​​e SQL命令查询表。 如有任何疑问，请随时在下面的评论部分中提问。

翻译自: https://www.sqlshack.com/the-collate-sql-command-overview/