Creating SQL Server index on a nvarchar column

Question

When I run this SQL statement:

CREATE UNIQUE INDEX WordsIndex ON Words (Word ASC);

I get the following exception message:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Words' and the index name 'WordsIndex'. The duplicate key value is (ass). The statement has been terminated.

The 'Word' column has a datatype of nvarchar(100).

There are two items in the 'Word' column that SQL Server interprets as the same: 'aß' and 'ass', which causes the indexing failure.

Why would SQL Server interpret those two different words as the same word?

Answer 1

It's using the default collation (in which those words equate to the same).

You need to explicitly qualify the collation you want to use on that column, in the column's table definition.

See ALTER TABLE

Answer 2

The duplicate is due to the collation of the column. The following query will tell you the collation being used:

Select COLLATION_NAME
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME = 'WordsIndex'
    And COLUMN_NAME = 'Words'

In addition, in German, 'ß' is equivalent to 'ss'. Thus, if you are using a Western European collation (e.g. SQL_Latin1_General_CP1_CI_AS), it will know that these are equivalent.

http://en.wikipedia.org/wiki/%C3%9F