如何摆脱SQL Server查询中的排序规则冲突? [英] How to get rid of 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
在上述查询中, 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
.
基本上这里发生的是每个数据库都有自己的排序规则,为您的数据提供排序规则,大小写和重音敏感性属性 href =http://technet.microsoft.com/en-us/library/ms143726.aspx> 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屋!