SQL Server Identity Functions

Identity values are handled by a number of functions in Microsoft SQL Server. The functions assist in establishing the automatic sequential value for table rows, identifying the initial seed value supplied when creating an identity column in a table or view, and returning the most recent identity value generated for a table or view. Any session and scope can have the last identity value created.

Let’s see how these functions work.

CREATE TABLE [dbo].[tbStudentDetails]
([StudentID] INT IDENTITY(1,2),
 [StudentName] VARCHAR(50) NULL
)

INSERT [dbo].[tbStudentDetails] VALUES
(‘Zafar Iqbal’),(‘Tahir Iqbal’),(‘Danial Hussain’)
GO

SELECT * FROM tbStudentDetails

It returns the following output.
1 Zafar Iqbal
3 Tahir Iqbal
5 Danial Hussain

The seed value of identity column created with the above script is 1. Lets query this value using IDENT_SEED.
SELECT IDENT_SEED(‘dbo.tbStudentDetails’)

The following will return the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.
SELECT IDENT_CURRENT(‘dbo.tbStudentDetails’)

The following will return the increment value specified when creating a table or view’s identity column.
SELECT IDENT_INCR(‘dbo.tbStudentDetails’)

Output:

Hope you find this article helpful.

Please subscribe for more interesting updates.