Index Options and Locking Modes

Allpages-locked and data-only-locked tables are supported by certain index options. On data-only-locked tables, the ignore_dup_row and allow_dup_row options are enforced during create index, but are not enforced during insert and update operations.

Data-only-locked tables always allow the insertion of duplicate rows.

Index Type

Allpages-Locked Table

Data-Only-Locked Table

During Index Creation

During Inserts

Clustered

allow_dup_row, ignore_dup_row

allow_dup_row, ignore_dup_row

allow_dup_row

Unique clustered

ignore_dup_key

ignore_dup_key

ignore_dup_key

Nonclustered

None

None

None

Unique nonclustered

ignore_dup_key

ignore_dup_key

ignore_dup_key

This table shows the behavior of commands that attempt to insert duplicate rows into tables with clustered indexes, and when the clustered indexes are dropped and re-created.

Options

Allpages-Locked Table

Data-Only-Locked Table

No options specified

Insert fails with error message 2615. Re-creating the index succeeds.

Insert succeeds. Re-creating the index fails with error message 1508.

allow_dup_row

Insert and re-creating the index succeed.

Insert and re-creating the index succeed.

ignore_dup_row

Insert fails with “Duplicate row was ignored” message. Re-creating the index succeeds.

Insert succeeds. Re-creating the index deletes duplicate rows.