Wednesday, 4 July 2007

Understanding IDENTITY Column in SQL Server

This article covers everything I know about them. I'll cover creating them, populating them, resetting them and a few other goodies.

Creating an Identity Column

In its simplest form an identity column creates a numeric sequence for you. You can specify a column as an identity in the CREATE TABLE statement:

CREATE TABLE tblUser(UserID int identity(1,1), Name varchar(20) )

The identity clause specifies that the column UserID is going to be an identity column. The first record added will automatically be assigned a value of 1 (the seed) and each subsequent record will be assigned a value 1 higher (the increment) than the previous inserted row. Identity columns can be int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0 (i.e. no places to the right of the decimal).

Populating the Table

When you insert into a table with an identity column you don't put a value into the identity column.

insert tblUser(Name) values ('sanjeev')
insert tblUser (Name) values ('sharma')

select * from TblUser
returns
UserID Name
------ --------------------
1 sanjeev
2 sharma

The value for UserID was automatically filled in. If you do try to fill in a value for an identity column it will give you an error:

insert tblUser (UserID, Name) values (3, 'Test')
returns
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'TblUser' when IDENTITY_INSERT is set to OFF.

Finding your Identity

If you want to see what identity value was just inserted you can use @@IDENTITY.

insert TblUser (Name) values ('Samjha)
select @@identity as NewRec
returns
NewRec
----------------
3

@@IDENTITY contains the last identity value generated by your statement. If you insert into a table that runs a trigger and generates another identity value, you will get back the last value generated in any table. To solve this problem you'll need to use SCOPE_IDENTITY to return the inserted value. Every procedure, trigger, function and batch is its own scope. SCOPE_IDENTITY shows the most recently inserted IDENTITY in the current scope (which ignores any triggers that might fire).

Select SCOPE_IDENTITY() as SameRecord
returns
SameRecord
----------------
3

How can I reset an Identity column and not start where it left?

If you delete all the records from a table it won't reset the identity.

Delete From tblUser
Insert TblUser (Name) Values ('New’)
select @@identity

returns the inserted identity as 4.
To reset the identity seed you need to use a DBCC command.

Delete From tblUser
DBCC CHECKIDENT('TblUser', RESEED, 0)

You can also run DBCC CHECKIDENT without specifying a reseed value. If the current seed is lower than the highest value in the table, the seed is updated to the highest value in the table.
SQL Server makes no attempt to guarantee sequential gap-free values in identity columns. If records are deleted SQL Server won't go back and populate using those values.


kick it on DotNetKicks.com

3 comments:

  1. I am using SQL Server 2005 and I had this issue where I wanted to control the identity column as I was ending up with id's in the 4 figures which wasn't what I really wanted for lookup tables.

    To get around this I simply inserted one new record into the table.

    This created my first record with an id of 1. After that I did a mass insert and hey presto everything is in sequence.

    ReplyDelete