Identity column

• An identity column has a name, initial seed and step.
• An identity column is not guaranteed to be unique nor consecutive
• We can find the current identity seed and change it by using dbcc checkident
• The next value allocated is the step added to the current seed.
• An insert failure can change the current seed value.
• An explicit value may be inserted via set identity_insert on and including the column list.
• A value explicitly inserted that is more than the current seed in the direction of the step will update the current seed.
• A truncate table (but not delete) will update the current seed to the original seed value.
• Scope_identity() can be used to find the last identity value allocated.
• An identity column can be added to a table but the identity property of an existing column cannot be changed.
• The identity function may be used to create an identity column on a table created using select into.
• Identity columns and their properties can be found via sys.identity_columns.
• Bulk insert cannot be guaranteed to allocate the identity values in the order of rows in a text file.
• It is sometimes easier to use a view to bulk insert into a table with an identity column.

To change an existing column to identity column, it should not contain null values. The identity properties are stored separately in metadata. If a value is used in identity and increment is called, then the new seed value will be set. No operation, including Rollback, Failover, ..... can change the seed value except DBCC reseed. Failover applies for the table object, but no the identity object.