Understanding Identity Columns

By Bill Graziano on 9 March 2002 | Tags: Identity


Karla writes "How can I reset an Identity column and not start where it left?" I've been getting quite a few questions about identity columns lately. This article should cover everything I know about them. I'll cover creating them, populating them, resetting them and a few other goodies. (This article has been updated through SQL Server 2005.)

Creating an Identity Column

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 dbo.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 INTO dbo.Yaks (YakName) values ('Gertrude')
INSERT INTO dbo.Yaks (YakName) values ('Helga')

SELECT	YakID, YakName
FROM	dbo.Yaks

- - - - - - - - - - - - - - - - - - - - - - - - 

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 INTO dbo.Yaks (YakID, YakName) values (5, 'Sam')

- - - - - - - - - - - - - - - - - - - - - - - - 

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 the Identity Value that was Inserted

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

INSERT INTO dbo.Yaks (YakName) values ('Sam the Yak')
SELECT SCOPE_IDENTITY() as NewRec

- - - - - - - - - - - - - - - - - - - - - - - - 

NewRec
----------------
11

Older applications might use @@IDENTITY to return the last identity value inserted. If you use @@IDENTITY and insert into a table that runs a trigger and generates another identity value, you will get back the last value generated in any table. That's why you 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). SCOPE_IDENTITY is only available in SQL Server 2000 and higher. 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 and higher only).

SELECT IDENT_CURRENT('Yaks') as SameRecordAgain

- - - - - - - - - - - - - - - - - - - - - - - - 

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.

Inserting Explicit Values into an Identity Column

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 INTO dbo.Yaks (YakID, YakName) Values(1, 'Mac the Yak')
SET IDENTITY_INSERT Yaks OFF
SELECT * from yaks

- - - - - - - - - - - - - - - - - - - - - - - - 

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.

Deleting rows from a table with an Identity Column

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

DELETE FROM dbo.Yaks
INSERT INTO dbo.Yaks (YakName) Values ('New Herd')
SELECT SCOPE_IDENTITY()

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

DELETE FROM dbo.Yaks
DBCC CHECKIDENT('Yaks', RESEED, 7)
INSERT INTO dbo.Yaks (YakName) Values ('New Herd')
SELECT SCOPE_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. Which also FINALLY answered Karla's question. 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. Books Online has additional detailed information about identity columns.

Other Notes

We have two articles that might be interesting.  The first is on creating a sequential record number field on existing data.  The second is on custom auto-generated sequences.  The second article is newer and much more interesting.  You can also see our additional articles on identity columns.


Related Articles

Efficiently Reuse Gaps in an Identity Column (9 February 2010)

How to Insert Values into an Identity Column in SQL Server (6 August 2007)

Custom Auto-Generated Sequences with SQL Server (24 April 2007)

Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts (14 August 2006)

Identity and Primary Keys (28 February 2001)

Alternatives to @@IDENTITY in SQL Server 2000 (19 September 2000)

Uniqueidentifier vs. IDENTITY (12 September 2000)

Returning @@IDENTITY back to an ASP Page (18 August 2000)

Other Recent Forum Posts

SQL Server Configuration Manager Not Usable (13h)

Select a single row based on conditions in multiple rows (1d)

I want Help Managing Big Data Sets in T SQL Efficiently (2d)

SQL stored procedure to load the error and correct record based on some business rules (2d)

Query is running too long (2d)

Sql Query to check status change of an item (3d)

Can I create differential backups tied to a specifc Full backup instead of the most recent? (8d)

My informix Sql query retruns Null always (9d)

- Advertisement -