代码Understanding Identity Columns
By Bill Graziano on 09 March 2002 | 7 Comments | 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.
By Bill Graziano on 09 March 2002 | 7 Comments | 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.
