Considerations when using ignore_dup_row and allow_dup_row options.
The ignore_dup_row and allow_dup_row options are irrelevant when you create a nonunique, nonclustered index. The SAP ASE server attaches a unique row identification number internally in each nonclustered index; duplicate rows are not a problem, even for identical data values.
ignore_dup_row and allow_dup_row are mutually exclusive.
In all-pages-locked tables, nonunique clustered indexes allows duplicate keys, but does not allow duplicate rows unless you specify allow_dup_row. This behavior differs for data-only-locked tables.
allow_dup_row allows you to create a nonunique, clustered index on a table that includes duplicate rows. If a table has a nonunique, clustered index that was created without the allow_dup_row option, you cannot create new duplicate rows using the insert or update command.
If any index in the table is unique, the requirement for uniqueness takes precedence over the allow_dup_row option. You cannot create an index with allow_dup_row if a unique index exists on any column in the table.
The ignore_dup_row option is also used with a nonunique, clustered index. The ignore_dup_row option eliminates duplicates from a batch of data. ignore_dup_row cancels any insert or update that would create a duplicate row, but does not roll back the entire transaction.
This table illustrates how allow_dup_row and ignore_dup_row affect attempts to create a nonunique, clustered index on a table that includes duplicate rows and attempts to enter duplicate rows into a table.
|
Option Setting |
Create an Index on a Table That Has Duplicate Rows |
Insert Duplicate Rows into a Table With an Index |
|---|---|---|
|
Neither option set |
create index fails. |
insert fails. |
|
allow_dup_row set |
create index completes. |
insert completes. |
|
ignore_dup_row set |
Index is created, but duplicate rows are deleted; error message. |
All rows are inserted, except duplicates; error message. |
This table shows which index options you can use with the different types of indexes:
|
Index Type |
Options |
|---|---|
|
Clustered |
ignore_dup_row | allow_dup_row |
|
Unique, clustered |
ignore_dup_key |
|
Nonclustered |
None |
|
Unique, nonclustered |
ignore_dup_key |