Hi,
I'm having a table (an existing table with data in it) and that table has a column UserName. I want this UserName to be unique. So I add a constraint like this:
ALTER TABLE Users
ADD CONSTRAINT [IX_UniqueUserUserName] UNIQUE NONCLUSTERED ([UserName])
Now I keep getting the Error that duplicate users exist in this table. But I have checked the database using the following query:
SELECT COUNT(UserId) as NumberOfUsers, UserName
FROM Users
GROUP BY UserName, UserId
ORDER BY UserName
This results in a list of users all having 1 as a NumberOfUsers. So no duplicates there. But when I'm checking the username he fails I see the following result:
beluga
béluga
So apperently he fails to compare an "e" and "é" or "è" ... It's like he ignores these, is there any way that sql doesn't ignore these accents when adding the unique key contraint.
Kind Regards,
Sem
SOLUTION:
THX to you guys I've found the solution. This fixed the problem:
ALTER TABLE Users
ALTER COLUMN UserName nvarchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS