Error 1505
Severity Level 14
Message Text
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID %d. Most significant primary key is '%S_KEY'.

Explanation
This error occurs when you attempt to create a unique index and more than one row contains the duplicate value. Microsoft® SQL Server™ has detected a uniqueness violation and cancels the CREATE INDEX operation.

For example, the creation for the index below would be canceled due to the uniqueness violation at row 3. The BUCHANAN STEVEN combination is already located in row 1.


TABLE: tblTest
Row strLastName strFirstName strCity strState
1 BUCHANAN STEVEN BOISE ID
2 SUYAMA MICHAEL BUTTE MT
3 BUCHANAN STEVEN SEATTLE WA
4 DAVOLIO NANCY SAN FRANCISCO CA

CREATE UNIQUE NONCLUSTERED INDEX
idxUniqueNames ON
tblText(strLastName, strFirstName)

Action
You must review your index objective. If your index does not need to be unique, remove the UNIQUE keyword and reissue the CREATE INDEX statement. However, if you still want to create a unique index, you must query the table in question and remove the rows in error. For more information about the CREATE INDEX statement, see CREATE INDEX.

To find the duplicate rows, issue a GROUP BY statement:

SELECT strLastName, strFirstName, count(*) as cnt FROM tblTest
GROUP BY strLastName, strFirstName HAVING count(*) > 1