What's the difference between SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP1_CI_AI

Question

i get this error when i run an update query in 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.

the query uses only 2 tables, the table it's updating and a temp table which it does an inner join into, neither table have i specified the collation of and they are both on the same database which means they should have the same collation since's it should be the database default one right

looking at the collations, the only difference is the last character, all i understand of the last part is that CI stands for Case Insensitive. if i was to take a stab in the dark i would think AI stands for Auto Increment but i have no idea what AS stands for

Answer 1

AI stands for accent insensitive (i.e. determines if cafe = café). You can use the collate keyword to convert one (or both) of the values' collations.
See link for more info: http://msdn.microsoft.com/en-us/library/aa258237(v=sql.80).aspx

Example: 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  

Example's Output:

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