Identity property is one of the most frequently used properties to generate auto increment value. I’ve seen sometime developer get confused with it. So this blog series will help developers to know everything about Identity property.
Syntax: IDENTITY [(seed, increment)]
You must specify both the seed and increment or neither. By default the value of seed and increment is (1, 1). Identity column must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0 and constrained to be not null.
The identity property on a column guarantees the following:
- Each new value is generated based on the current seed & increment value.
- Each new value for a particular transaction is different from other concurrent transactions on the table
Facts about identity property on a column :
- Identity property does not guarantee uniqueness of column value.
- A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. To get consecutive values the transaction should use an exclusive lock on the table or it should use the SERIALIZABLE isolation level.
- SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use a sequence generator with the NOCACHE option or use their own mechanism to generate key values.
- The identity values are not reused by the engine, If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again.
