Have Geek Brain

Create SQL Server Index on nvarchar Column

When I run this SQL statement:

CREATE UNIQUE INDEX WordsIndex ON Words (Word ASC);

I am getting the following exception message:

The CREATE UNIQUE INDEX operation completed because a duplicate key was found for the object name "dbo.Words" and the index name "WordsIndex". The value of the duplicate key is (back). The application has been completed.

The column "Word" is of the nvarchar (100) data type.

There are two items in the Word column that SQL Server interprets as the same: aß and ass, which causes the index to fail.

Why does SQL Server interpret these two different words as the same word?

+2


a source to share


2 answers


The duplicate is related to the sorting of the column. The following query will tell you what sorting is in use:

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


Also, in German, "ß" is equivalent to "ss". This way, if you use Western European collation (e.g. SQL_Latin1_General_CP1_CI_AS) it will know they are equivalent.

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

+4


a source


It uses the default collation (in which these words are equated to the same).

You need to explicitly specify the collation you want to use on that column in your column table definition.



See ALTER TABLE

0


a source






More articles:


All Articles