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


Top 5 Contributors of the Month
abhays
Clintonzz
mattyclown
MakarandK
caryfloric

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

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('', 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]
SELECT
@@IDENTITY AS [@@IDENTITY]


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(''),'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(''),'','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

 Subscribe to Articles

     

Further Readings:

Responses

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