Identity Property Part-1

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.




About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

Your email address will not be published.