IDENTITY columns in SQL Server
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 32767int
can go up to 2147483647 and bigint
can go up to 9223372036854775807.
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()
INTO tbUserMst(UserName,Password) VALUES('Mr. John','YYYY')
SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
@@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.
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.
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