如何解决SQL Server查询中的排序规则冲突？ [英] How to fix a collation conflict in a SQL Server query?

问题描述

我正在处理一个视图，其中正在对来自两个不同服务器的两个表使用内部联接。我们正在使用链接服务器。运行查询时，我收到以下消息：

I am working on a view, wherein I am using an inner join on two tables which are from two different servers. We are using linked server. When running the query I am getting this message:

在等于操作中无法解决 SQL_Latin1_General_CP1_CI_AS和 Arabic_CI_AS之间的排序规则冲突。

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Arabic_CI_AS" in the equal to operation.

我对整理并不了解。通过互联网搜索，我找到了使用 COLLATE 的解决方案，但是 COLLATE 的概念对我来说并不明确。它将对任何数据库进行任何更改吗？我正在寻找一种无需更改数据库内容的解决方案。

I don't know much about collation. Searching through internet I find solutions to use COLLATE, but the concept of COLLATE is not clear to me. Will it change anything for any of the databases? I am looking for a solution without changing anything for the databases.

欢迎使用适合这些概念的任何学习资料。

Any good learning material for these concepts is welcome.

推荐答案

您可以通过将查询中使用的排序规则强制为特定的排序规则来解决此问题，例如 SQL_Latin1_General_CP1_CI_AS 或 DATABASE_DEFAULT 。例如：

You can resolve the issue by forcing the collation used in a query to be a particular collation, e.g. SQL_Latin1_General_CP1_CI_AS or DATABASE_DEFAULT. For example:

SELECT MyColumn
FROM FirstTable a
INNER JOIN SecondTable b
ON a.MyID COLLATE SQL_Latin1_General_CP1_CI_AS = 
b.YourID COLLATE SQL_Latin1_General_CP1_CI_AS

在上述查询中，a。 MyID和b.YourID将是具有基于文本的数据类型的列。使用 COLLATE 将强制查询忽略数据库上的默认排序规则，而是使用提供的排序规则，在这种情况下，为 SQL_Latin1_General_CP1_CI_AS 。

In the above query, a.MyID and b.YourID would be columns with a text-based data type. Using COLLATE will force the query to ignore the default collation on the database and instead use the provided collation, in this case SQL_Latin1_General_CP1_CI_AS.

基本上，这里发生的是每个数据库都有自己的归类，它们为数据提供排序规则，大小写和重音敏感度属性（来自 http://technet.microsoft.com/en-us/library/ms143726.aspx ）并应用于具有文本数据类型的列，例如 VARCHAR ， CHAR ， NVARCHAR 等。当有两个数据库时具有不同的排序规则，如果不解决两个不同的排序规则之间的冲突，则无法将文本列与等于（=）的运算符进行比较。

Basically what's going on here is that each database has its own collation which "provides sorting rules, case, and accent sensitivity properties for your data" (from http://technet.microsoft.com/en-us/library/ms143726.aspx) and applies to columns with textual data types, e.g. VARCHAR, CHAR, NVARCHAR, etc. When two databases have differing collations, you cannot compare text columns with an operator like equals (=) without addressing the conflict between the two disparate collations.

这篇关于如何解决SQL Server查询中的排序规则冲突？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！