如何在 SQL Server 中设置连接的排序规则？
如何设置 SQL Server 在该连接期间将使用的排序规则？
直到我连接到 SQL Server 后我才知道我想使用什么排序规则。
例如，语言为 fr-IT 的浏览器已连接到该网站。我在该连接上运行的任何查询都希望遵循法语、意大利变体排序规则。
我设想了一个假设的连接级别属性，类似于SET ANSI_NULLS OFF，但对于排序规则1：
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 在比较和排序字符串时使用的排序规则。
如何指定每个连接的排序规则？
另请参阅
1 出现区域设置问题的假设 SQL
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 collation1:
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
1 hypothetical sql that exhibits locale issues
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
正如 marc_s 评论的，排序规则是数据库或列的属性，而不是连接的属性。
但是，您可以使用 COLLATE 关键字覆盖语句级别的排序规则。
使用你的例子：
仍然，我找不到关于使用 COLLATE 和动态排序规则名称的语句，留下唯一可能的解决方案动态 SQL 和 EXEC。请参阅此social.MSDN条目 为例。
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:
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.