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?

Sounds like a homework question?

keith
– keith

2010-04-27 02:25:06 +00:00
Commented Apr 27, 2010 at 2:25 — keith
– keith, Commented Apr 27, 2010 at 2:25

Thomas · Accepted Answer · 2010-04-27 01:54:03Z

4

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

answered Apr 27, 2010 at 1:54

Thomas

64.8k12 gold badges98 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mitch Wheat · Accepted Answer · 2010-04-27 01:29:47Z

0

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

edited Apr 27, 2010 at 1:29

answered Apr 27, 2010 at 1:23

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Collectives™ on Stack Overflow

Creating SQL Server index on a nvarchar column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related