.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

IDENTITY columns in SQL Server

Posted By:Prasanti Prusty       Posted Date: December 23, 2009    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  

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.

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


Detecting Identity Columns And Their Properties

To find the last identity value allocated we can use Scope_identity()
Example :
tbUserMst(UserName,Password) VALUES('Mr. John','YYYY')

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.


A column has the identity property or not can be checked as follows, Which will return 1 if the column has the identity property.


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.

ColumnName = name ,
OriginalSeed= seed_value ,
Step= increment_value ,
LastValue= last_value ,
IsNotForReplication= is_not_for_replication FROM sys.identity_columns

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend