The identity in nonunique index database option automatically includes an IDENTITY column in a table’s index keys so that all indexes created on the table are unique. This option makes logically nonunique indexes internally unique and allows them to process updatable cursors and isolation level 0 reads.
To enable identity in nonunique indexes, enter:
sp_dboption pubs2, "identity in nonunique index", true
The table must already have an IDENTITY column, either from a create table statement or by setting the auto identity database option to true before creating the table.
Use identity in nonunique index to use cursors and isolation level 0 reads on tables with nonunique indexes. A unique index ensures that the cursor is positioned at the correct row the next time a fetch is performed on that cursor.
For example, after setting identity in nonunique index and auto identity to true, suppose you create the following table, which has no indexes:
create table title_prices (title varchar(80) not null, price money null)
sp_help shows that the table contains an IDENTITY column, SYB_IDENTITY_COL, which is automatically created by the auto identity database option. If you create an index on the title column, use sp_helpindex to verify that the index automatically includes the IDENTITY column.
