SQL Server IDENTITY: read, insert and reseed values
Compare @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT(), insert an explicit IDENTITY value and reseed with DBCC CHECKIDENT.
SQL Server offers three functions that return the last IDENTITY value generated, each with a different scope. This article summarises them, then shows how to insert explicit values and reseed a column. The descriptions are rewritten from the Microsoft Learn reference pages: @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT() and DBCC CHECKIDENT.
Retrieve the last IDENTITY value
| Function | Returns the last IDENTITY value generated... |
|---|---|
@@IDENTITY |
on the connection, for any table and any scope |
SCOPE_IDENTITY() |
on the connection, within the current scope, for any table |
IDENT_CURRENT('table') |
in the given table, for any connection and any scope |
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT IDENT_CURRENT('dbo.Tool')
Use SCOPE_IDENTITY() after an insert when the table has triggers or when the statement runs in a nested scope, because @@IDENTITY can return a value generated by a trigger.
Insert an explicit value
SET IDENTITY_INSERT allows an explicit value in an identity column. It is set per table and must be turned off afterwards.
SET IDENTITY_INSERT dbo.Tool ON
GO
-- Insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
Reseed an identity column
DBCC CHECKIDENT ... RESEED sets the current identity value. The next generated value is the reseed value plus the increment.
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 100)
GO
This sets the current value to 100, so the next insert gets 101. To make the next generated value 1 on an empty table with an increment of 1, reseed to 0.
Notes
- The table names (
dbo.Tool,pubs.jobs) are examples.