LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon