When the unique auto_identity index option is set to true, it adds an IDENTITY column with a unique, nonclustered index to new tables. By default, the IDENTITY column is a 10-digit numeric datatype, but you can change this default with the size of auto identity column configuration parameter.
Though you can set unique auto_identity index to true in tempdb, it is not recognized or used, and temporary tables created there do not automatically include an IDENTITY column with a unique index.
The unique auto_identity index option provides a mechanism for creating tables that have an automatic IDENTITY column with a unique index that can be used with updatable cursors. The unique index on the table ensures that the cursor is positioned at the correct row after a fetch. (If you are using isolation level 0 reads and need to make logically nonunique indexes internally unique so that they can process updatable cursors, use the identity in nonunique index option.)
In some cases, the unique auto_identity index option can avoid the Halloween Problem for the following reasons:
Users cannot update an IDENTITY column; hence, it cannot be used in the cursor update.
The IDENTITY column is automatically created with a unique, nonclustered index so that it can be used for the updatable cursor scan.
For more information about the Halloween Problem, IDENTITY columns, and cursors, see the Transact-SQL User’s Guide.
Do not confuse the unique auto_identity index option with the identity in nonunique index option, which is used to make all indexes in a table unique by including an IDENTITY column in the table’s index keys.
