SQL SERVER – Find Current Identity of Table

Many times we need to know what is the current identity of the column. I have found one of my developer using aggregated function MAX() to find the current identity.

USE AdventureWorks
FROM Person.Address

However, I prefer following DBCC command to figure out current identity.

USE AdventureWorks
DBCC CHECKIDENT ('Person.Address')

Reference : Pinal Dave (http://blog.SQLAuthority.com)

7 thoughts on “SQL SERVER – Find Current Identity of Table

  1. hi,

    i tried it but it gives me error.
    but when i am just giving the table name it gives the correct result

    i think it should be like this

    USE Name_Of_The_DB
    DBCC CHECKIDENT (‘Name_Of_The_Table’)

    please check as when i given the column name it gives me error

    What is Person.Address???
    i didnot get it plz help………..


  2. @@Identity should never be used. Even IDENT_CURRENT() is not recommended, since other transactions can insert rows in the meantime. Use SCOPE_IDENTITY() to retrieve identity value of a row just inserted. If there is more than one row, use SCOPE_IDENTITY() in compination with OUTPUT clause to collect all ID’s. Using sequence objects in Denali is another possible option. Here is complete example for OUTPUT clause:

    CREATE TABLE t(x int IDENTITY(1,1), y int)

    DECLARE @TableOfInsertedIds TABLE ( id int )

    INSERT INTO t( y ) — inserts into “t”
    OUTPUT INSERTED.x INTO @TableOfInsertedIds( id ) — this populates our table variable
    SELECT TOP 10 object_id FROM sys.all_objects

    SELECT * FROM @TableOfInsertedIds


  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

  4. Hi Pinal

    this DBCC CHECKIDENT (‘Person.Address’) will give the following result

    Checking identity information: current identity value ’22’, current column value ’22’.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    and this can not be used in any query to get the output in any programmin language, so i think your above query is not worth.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s