Severity Level 14
'CREATE INDEX terminated because a duplicate row was found. Primary key is ''%S_KEY''.'
This error occurs when you attempt to create a clustered index and a duplicate row is encountered in the table.
The following example produces error 1508 when the index creation process reaches row 3, which is an exact duplicate of row 1:
TABLE: tblTest Row strLastName strFirstName strCity strState 1 BUCHANAN STEVEN BOISE ID 2 SUYAMA MICHAEL BUTTE MT 3 BUCHANAN STEVEN BOISE ID 4 DAVOLIO NANCY SAN FRANCISCO CA CREATE CLUSTERED INDEX idxClusteredName ON tblText(strLastName)
You must decide whether to allow or prevent duplicate rows in the table. To allow duplicate rows, you should add the ALLOW_DUP_ROW keyword to the CREATE INDEX statement. Be cautious when using IGNORE_DUP_ROW, because it physically removes duplicate data from the table. Also note that when creating clustered indexes, the amount of space required can be 120 percent to 150 percent of the original table size. For more information, see CREATE INDEX.
The following example creates the clustered index while allowing the duplicate rows to remain in the table:
CREATE CLUSTERED INDEX idxClusteredName ON tblText(strLastName) WITH ALLOW_DUP_ROW