tsql - SQL_Latin1_General_CP1_CI_AS和SQL_Latin1_General_CP1_CI_AI有什么区别

Question

在Microsoft SQL Server中运行更新查询时出现此错误

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

该查询仅使用2个表，正在更新的表和进行内部联接的临时表，两个表都没有指定排序规则，并且它们都在同一数据库中，这意味着它们应该具有相同的排序规则，因为它应该是数据库默认的一项权利

从排序规则来看，唯一的区别是最后一个字符，我所了解的最后一部分是CI代表不区分大小写。如果我要在黑暗中刺痛，我会认为AI代表自动增量，但我不知道AS代表什么

Answer 1

AI代表对重音不敏感(即确定cafe =café)。
您可以使用collat​​e关键字转换值中的一个(或两个)排序规则。
有关更多信息，请参见链接:http://msdn.microsoft.com/en-us/library/aa258237(v=sql.80).aspx

示例:DBFiddle

--setup a couple of tables, populate them with the same words, only vary whether to accents are included
create table SomeWords (Word nvarchar(32) not null)
create table OtherWords (Word nvarchar(32) not null)

insert SomeWords (Word) values ('café'), ('store'), ('fiancé'), ('ampère'), ('cafétería'), ('fête'), ('jalapeño'), ('über'), ('zloty'), ('Zürich')
insert OtherWords (Word) values ('cafe'), ('store'), ('fiance'), ('ampere'), ('cafétería'), ('fete'), ('jalapeno'), ('uber'), ('zloty'), ('Zurich')

--now run a join between the two tables, showing what comes back when we use AS vs AI.
--NB: Since this could be run on a database of any collation I've used COLLATE on both sides of the equality operator
select sw.Word MainWord
, ow1.Word MatchAS
, ow2.Word MatchAI
from SomeWords sw
left outer join OtherWords ow1 on ow1.Word collate SQL_Latin1_General_CP1_CI_AS = sw.Word collate SQL_Latin1_General_CP1_CI_AS 
left outer join OtherWords ow2 on ow2.Word collate SQL_Latin1_General_CP1_CI_AI = sw.Word collate SQL_Latin1_General_CP1_CI_AI  

示例输出:
MainWord MatchAS MatchAI café cafe store store store fiancé fiance ampère ampere cafétería cafétería cafétería fête fete jalapeño jalapeno über uber zloty zloty zloty Zürich Zurich