如何在SQL Server中为连接设置排序规则? [英] How to set collation for a connection in SQL Server?

问题描述

如何设置在连接期间SQL Server将使用的排序规则?

直到我连接到SQL Server之前，我才知道要使用什么排序规则.

例如具有fr-IT语言的浏览器已连接到该网站.我在该连接上运行的所有查询都希望遵循法语，义大利语变体的排序规则.

我设想了一个假设的连接级别属性，与SET ANSI_NULLS OFF类似，但是对于排序规则 ¹ :

SET COLLATION_ORDER 'French_CI_AS'

SELECT TOP 100 FROM Orders
ORDER BY ProjectName

及以后

SELECT * FROM Orders
WHERE CustomerID = 3277 
AND ProjectName LIKE '%l''ecole%'

及以后

UPDATE Quotes
SET IsCompleted = 1
WHERE QuoteName = 'Cour de l''école'

与此同时，当中文客户连接时:

SET COLLATION_ORDER Chinese_PRC_CI_AI_KS_WS

SELECT TOP 100 FROM Orders
ORDER BY ProjectName

或

SELECT * FROM Orders
WHERE CustomerID = 3277 
AND ProjectName LIKE '學校'

或

UPDATE Quotes
SET IsCompleted = 1
WHERE QuoteName = '學校的操場'

---

现在，我可以更改系统中的每个SELECT语句，以允许我传递排序规则:

SELECT TOP 100 FROM Orders
WHERE CustomerID = 3278
ORDER BY ProjectName COLLATE French_CI_AS

但是您不能将排序规则作为参数传递给存储过程:

CREATE PROCEDURE dbo.GetCommonOrders 
   @CustomerID int, @CollationOrder varchar(50)
AS

SELECT TOP 100 FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY ProjectName COLLATE @CollationOrder

在执行UPDATE或SELECT时，COLLATE子句无法帮助我.

注意:数据库中的所有字符串列均已为nchar，nvarchar或ntext.我不是在谈论适用于服务器，数据库，表或非Unicode列(即char，varchar，text)的列的默认排序规则.我说的是SQL Server在比较和排序字符串时使用的排序规则.

---

如何指定每个连接排序规则?

另请参见

• 类似的问题，但适用于ADO.网络和连接字符串
• 类似的问题，但适用于ASP.net MVC2和MySQL

¹ 展示语言环境问题的假设sql

解决方案

正如marc_s所说，排序规则是数据库或列的属性，而不是连接的属性.

但是，您可以使用 COLLATE 关键字在语句级别覆盖排序规则.

>

使用您的示例:

SELECT * FROM Orders
WHERE CustomerID = 3277 
AND ProjectName COLLATE Chinese_PRC_CI_AI_KS_WS LIKE N'學校'

UPDATE Quotes
SET IsCompleted = 1
WHERE QuoteName COLLATE Chinese_PRC_CI_AI_KS_WS = N'學校的操場'

仍然，我找不到有关使用带有动态排序规则名称的COLLATE的声明，仅留下了动态SQL和EXEC的解决方案.有关示例，请参见此social.MSDN条目.

How can i set the collation SQL Server will use for the duration of that connection?

Not until i connect to SQL Server do i know what collation i want to use.

e.g. a browser with language fr-IT has connected to the web-site. Any queries i run on that connection i want to follow the French language, Italy variant collation.

i envision a hypothetical connection level property, simlar to SET ANSI_NULLS OFF, but for collation¹:

SET COLLATION_ORDER 'French_CI_AS'

SELECT TOP 100 FROM Orders
ORDER BY ProjectName

and later

SELECT * FROM Orders
WHERE CustomerID = 3277 
AND ProjectName LIKE '%l''ecole%'

and later

UPDATE Quotes
SET IsCompleted = 1
WHERE QuoteName = 'Cour de l''école'

At the same time, when a chinese customer connects:

SET COLLATION_ORDER Chinese_PRC_CI_AI_KS_WS

SELECT TOP 100 FROM Orders
ORDER BY ProjectName

or

SELECT * FROM Orders
WHERE CustomerID = 3277 
AND ProjectName LIKE '學校'

or

UPDATE Quotes
SET IsCompleted = 1
WHERE QuoteName = '學校的操場'

---

Now i could alter every SELECT statement in the system to allow me to pass in a collation:

SELECT TOP 100 FROM Orders
WHERE CustomerID = 3278
ORDER BY ProjectName COLLATE French_CI_AS

But you cannot pass a collation order as a parameter to a stored procedure:

CREATE PROCEDURE dbo.GetCommonOrders 
   @CustomerID int, @CollationOrder varchar(50)
AS

SELECT TOP 100 FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY ProjectName COLLATE @CollationOrder

And the COLLATE clause can't help me when performing an UPDATE or a SELECT.

Note: All string columns in the database all are already nchar, nvarchar or ntext. i am not talking about the default collation applied to a server, database, table, or column for non-unicode columns (i.e. char, varchar, text). i am talking about the collation used by SQL Server when comparing and sorting strings.

---

How can i specify per-connection collation?

See also

• Similar question, but for ADO.net and connection strings
• Similar question, but for ASP.net MVC2 and MySQL

¹ hypothetical sql that exhibits locale issues

解决方案

As marc_s commented, the collation is a property of a database or a column, and not of a connection.

However, you can override the collation on statement level using the COLLATE keyword.

Using your examples:

SELECT * FROM Orders
WHERE CustomerID = 3277 
AND ProjectName COLLATE Chinese_PRC_CI_AI_KS_WS LIKE N'學校'

UPDATE Quotes
SET IsCompleted = 1
WHERE QuoteName COLLATE Chinese_PRC_CI_AI_KS_WS = N'學校的操場'

Still, I cannot find a statement on using COLLATE with a dynamic collation name, leaving as only possible solution dynamic SQL and EXEC. See this social.MSDN entry for an example.

这篇关于如何在SQL Server中为连接设置排序规则?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！