Unicode column filtering on Sql 2005 server - to map data

Question

I have a simple table:

CREATE TABLE [dbo].[Users]([Surname] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL) ON [PRIMARY]

with two lines:

Paweł
Pawel

Issue the following select statement:

SELECT *, CAST(Surname AS VARBINARY(30)) AS Expr1, CAST(N'Paweł' AS VARBINARY(30)) AS Expr1 
FROM Users WHERE Surname = N'Paweł'

gives the following result:

Paweł   0x50006100770065004201  0x50006100770065004201
Pawel   0x50006100770065006C00  0x50006100770065004201

A string with the value "Pawel" is returned, although it is not equal to "Paweł" (binary columns show this).

Any idea what happens, why does SQL Server consider Pawel = Paweł in this case?

Thanks Paweł

Answer 1

Your collation is insensitive to accent: Latin1_General_CI_AI

Try something like this:

select 1 where N'Pawel' = N'Paweł' collate Latin1_General_100_CI_AS

Returns nothing as expected.

Answer 2

"AI" "Accent Insensitive". , "AS" "Accent Sensitive": Latin1_General_CI_AS