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

sql-server-2005 tsql indexing unique-index
Alex Angas picture Alex Angas · Feb 2, 2010 · Viewed 61.5k times · Source

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?

Answer

martin clayton picture martin clayton · Feb 2, 2010

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.

Related questions

How important is the order of columns in indexes?

I've heard that you should put columns that will be the most selective at the beginning of the index declaration. Example: CREATE NONCLUSTERED INDEX MyINDX on Table1 ( MostSelective, SecondMost, Least ) First off, is what I'm saying correct? If so, am …

Read More
Can I optimize a SELECT DISTINCT x FROM hugeTable query by creating an index on column x?

I have a huge table, having a much smaller number (by orders of magnitude) of distinct values on some column x. I need to do a query like SELECT DISTINCT x FROM hugeTable, and I want to do this relatively …

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More