Unable to create index because of duplicate that doesn't exist?

indexing, sql-server-2005, t-sql, unique-index

Solution

It's not that the index already exists, but that there are duplicate values of the `TopicShortName` field in the table itself. According to the error message the duplicate value is an empty string (it might just be a facet of posting I guess). Such duplicates prevent the creation of a `UNIQUE` index.

You could run a query to confirm that you have a duplicate:

SELECT
    TopicShortName,
    COUNT(*)
FROM
    DimMeasureTopic
GROUP BY
    TopicShortName
HAVING
    COUNT(*) > 1

Presumably in the other database the data are different, and the duplicates are not present.

Problem

I'm getting an error running the following Transact-SQL command: ``` CREATE UNIQUE NONCLUSTERED INDEX IX_TopicShortName ON DimMeasureTopic(TopicShortName) ``` The error is: Msg 1505, Level 16, State 1, Line 1 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.DimMeasureTopic' and the index name 'IX_TopicShortName'. The duplicate key value is (). When I run `SELECT * FROM sys.indexes WHERE name = 'IX_TopicShortName'` or `SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DimMeasureTopic]')` the IX_TopicShortName index does not display. So there doesn't appear to be a duplicate. I have the same schema in another database and can create the index without issues there. Any ideas why it won't create here?

Original source