How to get current maximum value of an identity column in a table
Introduction
In this article I will show you how to get current maximum value in the identity column of a table.
There is a command DBCC CHECKIDENT, which can be used to get the current maximum value of an identity column in a table.
Syntax: DBCC CHECKIDENT('TableName')
Let’s create a table and check how we can get the current maximum value of an identity column in a table.
CREATE TABLE TEST22
(
ID INT IDENTITY(1,1),
Name VARCHAR(20)
)
INSERT INTO TEST22(Name) SELECT 'Kamal'
INSERT INTO TEST22(Name) SELECT 'Rohit'
INSERT INTO TEST22(Name) SELECT 'Dilshad'
INSERT INTO TEST22(Name) SELECT 'Suman'
INSERT INTO TEST22(Name) SELECT 'Sam'
SELECT * FROM TEST22
OUTPUT
Now check the current max value of the identity column
DBCC CHECKIDENT('TEST22')
Here you can see in the output current max value of identity column is 5
OUTPUT
Now do some more experiment.
Lets delete some record and check the max value.
DELETE FROM TEST22 WHERE ID=3
DELETE FROM TEST22 WHERE ID=5
SELECT * FROM TEST22
Records left after deletion
OUTPUT
Now check the current max value of the identity column after deletion. It will show 5
DBCC CHECKIDENT('TEST22')
OUTPUT
Now add 2 records and then delete a record
INSERT INTO TEST22(Name) SELECT 'John Smith'
INSERT INTO TEST22(Name) SELECT 'Shaheen'
DELETE FROM TEST22 WHERE ID=7
Records left after new records added and one deleted
OUTPUT
Now if we check the current max value of the identity column after insertion and deletion operation we will get 7.
DBCC CHECKIDENT('TEST22')
OUTPUT
Enjoy coding............