SQLcommitted

Committed with SQL

SQL Server: 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 :image

  • 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.

Reference: MSDN

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

 

About these ads

August 8, 2013 - Posted by | Transact-SQL | , , , , ,

3 Comments »

  1. […] In this blog series, I’m covering all about Identity property. This is the second part of this series. You can read the first part here (SQL Server: Identity Property Part – 1). […]

    Pingback by SQL Server: Identity Property Part – 2 « SQLcommitted | December 4, 2013 | Reply

  2. Good One

    Comment by sanmaya | August 9, 2013 | Reply

  3. Good

    Comment by raghu | August 9, 2013 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 76 other followers

%d bloggers like this: