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. |