Microsoft SQL Server: Current Identity Column Value in a Table

What is the current identity column value in my table?

You probably have an identity column defined in some, or a lot of your SQL Server tables.  But do you know how to check the identity value information for one of your tables?  If not, then here is how you do it.  

To check the last identity value information for your table you can run the following command:

DBCC CHECKIDENT ( 'yourtable', NORESEED );

Where yourtable  is set to the table in which you what to check the identity value information.  When you run this command, it will show something like this:

Checking identity information: current identity value '1003', current column value '594'.

Here you can see two values are displayed “current identity value” and “current column value”.  The “current identity value” is the last identity value used when a new row was added to your table, where as the “current column value” is the highest identity value used on a row in your table.   The next time a row is added to this table the identity value 1004 will be used.  In this example a large number of rows have been deleted from the top end of the identity values.  That is why the current “current column value” is set to 594.  If the highest identify column value in the table is the same as the “current identity value” column then you would get this displayed when running the DBCC command above:

Checking identity information: current identity value '1003', current column value '1003'.

# # #

See All Articles by Columnist Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles