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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted April 11, 2019

Microsoft SQL Server: Current Identify Column Value in a Table

By Greg Larsen

What is the current identify 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 identify 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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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