User Id :    Password :      New Member   Forgot Password  
 
How to get current maximum value of an identity column in a table
Description There is a command DBCC CHECKIDENT, which can be used to get the current maximum value of an identity column in a table.   No. of Views     1201
  Rating     0
Author Shaheen   Posted On     27 Jul 2011
Tags SQL SERVER,SQL SERVER : How To    

Sample Code   Download Code

 

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............

 

 

About Author

About Author Coding is as cool as cooking......... Shaheen
No Photo
 
Country India
Company DotNetLogix
Home Page www.dotnetlogix.com

Rate this article

Rating options from poor, fair, good, very good to excelent.  
 

Comments

 
 
Posted By Najmul Hoda on 27 Aug 2011 at 12:56 PM
 

Thanks dear,
Happy coding.
Nice one....

 
 
 
Posted By Najmul Hoda on 27 Aug 2011 at 12:38 PM
 

nice one,


thanks for sharing....


 

 
 
   
Write your comment here.
Comment
Verification Code