IDENTITY columns in SQL Server

Chikul
Posted by in Sql Server category on for Beginner level | Views : 16427 red flag

The IDENTITY columns are auto incrementing columns provided by SQL Server.
There can only be one IDENTITY column per table.
SQL Server will take care of incrementing this column automatically.
Introduction

The IDENTITY columns are auto incrementing columns provided by SQL Server.
There can only be one IDENTITY column per table.
SQL Server will take care of incrementing this column automatically.

It has a name, initial seed and step.
When a row is inserted into a table the column will take the value of the current seed incremented by the step.

Disadvantage :

An Identity column is not guaranteed to be unique nor consecutive.
If any insert failure is there then also it can change the current seed value.
If we truncate a table (but not delete), It will update the current seed to the original seed value.
   

Identity Data types :

The identity must only contain integer values but the column can be of any numeric datatype (bigint, int, tinyint, numeric, decimal).
But ,We need to make sure identity column are not reaching the limit of their base data type.

An IDENTITY column of tinyint data type can go up to 255.
smallint can go up to 32767
int can go up to 2147483647 and
bigint can go up to 9223372036854775807.

Example :

If you created an IDENTITY column of smallint datatype, its values can go upto 32767.
If you try to insert anymore rows, you will get the following error:
Arithmetic overflow error converting IDENTITY to data type smallint.

So, We need to monitor these IDENTITY columns, to avoid getting into such problems. If you can see in advance, that an IDENTITY column is reaching its limit, then we could do something about it, before it reaches the limit.


Reset Identity column

DBCC CHECKIDENT('<tablename>', RESEED, 0)

Detecting Identity Columns And Their Properties


To find the last identity value allocated we can use Scope_identity()
Example :

INSERT INTO tbUserMst(UserName,Password) VALUES('Mr. John','YYYY')
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @@IDENTITY AS [@@IDENTITY]
GO

The existance of an identity column on a table can be checked as follows, Which will return 1 if an identity exists on the table.

SELECT OBJECTPROPERTY(OBJECT_ID('<tablename>'),'TableHasIdentity')

A column has the identity property or not can be checked as follows, Which will return 1 if the column has the identity property.
SELECT COLUMNPROPERTY(OBJECT_ID('<tablename>'),'<columnname>','IsIdentity')

A more useful way of obtaining this information is by using the catalog view sys.identity_columns which returns a row for each column in the database with an identity property.

SELECT TableName = OBJECT_NAME(OBJECT_ID) , ColumnName = name , OriginalSeed = seed_value , Step = increment_value , LastValue = last_value , IsNotForReplication = is_not_for_replication FROM sys.identity_columns
Page copy protected against web site content infringement by Copyscape

About the Author

Chikul
Full Name: Prasanti Prusty
Member Level: Bronze
Member Status: Member
Member Since: 11/20/2009 10:34:27 PM
Country: India

http://knowledgeparlour.blogspot.com/

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)