Understanding Identity Columns
In it's 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 Yaks ( YakID smallint identity(7,2), YakName char(20) )
The identity clause specifies that the column YakID is going to be an identity column. The first record added will automatically be assigned a value of 7 (the seed) and each subsequent record will be assigned a value 2 higher (the increment) than the previous inserted row. Most identity columns I see are specified as IDENTITY(1,1) but I used IDENTITY(7,2) so the difference would be clear. If you don't specify the identity and seed they both default to 1. 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 Yaks (YakName) values ('Gertrude')
insert Yaks (YakName) values ('Helga')
select *
from Yaks
returns
YakID YakName ------ -------------------- 7 Gertrude 9 Helga
The value for YakID was automatically filled in. If you do try to fill in a value for an identity column it will give you an error:
insert yaks (YakID, YakName) values (5, 'Sam')
returns
Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in
table 'Yaks' 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 Yaks (YakName) values ('Sam the Yak')
select @@identity as NewRec
returns
NewRec ---------------- 11
@@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 it's 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 ---------------- 11
SCOPE_IDENTITY is only available in SQL Server 2000. You can also see the most recent identity value for a table regardless of scope or session (process). You can use IDENT_CURRENT for that (again, SQL Server 2000 only).
Select IDENT_CURRENT('Yaks') as SameRecordAgain
returns
SameRecordAgain ---------------- 11
Notice that we had to pass in the table name to the IDENT_CURRENT function. This will give you the most recent identity value for that table regardless of who inserted it.
Maintenace
If you want to insert a value into an identity column you can use the SET IDENTITY_INSERT statement.
SET IDENTITY_INSERT Yaks ON Insert Yaks (YakID, YakName) Values(1, 'Mac the Yak') SET IDENTITY_INSERT Yaks OFF select * from yaks
returns
YakID YakName ------ -------------------- 7 Gertrude 9 Helga 11 Sam the Yak 1 Mac the Yak
You can only turn on IDENTITY_INSERT for one table per session so it's always a good idea to turn it off when you're done with it.
If you delete all the records from a table it won't reset the identity.
Delete From yaks
Insert Yaks (YakName) Values ('New Herd')
select @@identity
returns the inserted identity as 13. To reset the identity seed you need to use a DBCC command.
Delete From yaks
DBCC CHECKIDENT('Yaks', RESEED, 7)
Insert Yaks (YakName) Values ('New Herd')
select @@identity
This returns an identity of 9. After the seed value is reset to 7, it's incremented by 2 (as we specified when we created the table) for the next record. 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. It's also possible for an insert to fail and "use up" an identity value. We do have an article on creating a sequential record number. Books Online has additional detailed information about identity columns.
This article was originally published on SQLTeam.com. You can also see additional articles on identity columns on SQLTeam.com
Bill Graziano is a Microsoft SQL Server MVP and partner with ClearData Consulting, Inc. He specializes in Microsoft SQL Server database administration and development. He runs a SQL Server web site at SQLTeam.com.