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 for the identity in nonunique index database option to work, 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 if you plan 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.
