Create index aborted on duplicate rows. 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 |
DORR |
ROBERT |
IRVING |
TX |
2 |
DORR |
ROBERT |
LEWISVILLE |
TX |
3 |
DORR |
ROBERT |
IRVING |
TX |
4 |
DORR |
JOAN |
IRVING |
TX |
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 detailed information about the create index statement, see the Microsoft SQL Server Transact-SQL Reference.
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