Filtering unicode column in Sql server 2005 - to much data match

Question

Hi,

I have a simple table:

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

with two rows:

Paweł
Pawel

Issuing 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 following result:

Paweł   0x50006100770065004201  0x50006100770065004201
Pawel   0x50006100770065006C00  0x50006100770065004201

Row with value 'Pawel' is returned , even though it's not equal to 'Paweł' (binary columns show it).

Any idea what is going on, how come SQL server considers Pawel = Paweł in that case?

Thanks, Paweł

Answer 1

Your collation is accent insensitive: 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

The "AI" in your collation stands for "Accent Insensitive". To distinguish between the two, you'd want to use "AS" instead for "Accent Sensitive": Latin1_General_CI_AS