IDENTITY columns in SQL Server

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

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.