Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted April 11, 2019

Microsoft SQL Server: Current Identity Column Value in a Table

By Greg Larsen

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:


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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM