Error 1508

Severity Level 14

Message Text

Create index aborted on duplicate rows. Primary key is '%S_KEY'

Explanation

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)

Action

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