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
GO
SELECT MAX(AddressID)
FROM Person.Address
GO

SQL SERVER - Find Current Identity of Table identity1

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

USE AdventureWorks
GO
DBCC CHECKIDENT ('Person.Address')
GO

SQL SERVER - Find Current Identity of Table identity2

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – Famous Quotes From Bill Gates
Next Post
SQLAuthority News – White Paper: Security Overview for Database Administrators

Related Posts

9 Comments. Leave new

  • 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
    GO
    DBCC CHECKIDENT (‘Name_Of_The_Table’)
    GO

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

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

    Reply
  • even SELECT IDENT_CURRENT(‘table name’) gives the desired results

    Reply
  • or we can use @@identity

    Reply
  • @@Identity will not work. it will give the the last identity inserted in a transaction. i ll go with krishna.

    Reply
  • @@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:

    DROP TABLE t
    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 t
    SELECT * FROM @TableOfInsertedIds

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

    Reply
  • abdulhannanijaz
    January 8, 2016 10:48 am

    Hi
    Doesn’t the 2nd query also reset the identity if there is Gap between identity & Seed value?

    AS SAID on MSDN
    DBCC CHECKIDENT(‘Table Name’) or DBCC CHECKIDENT(‘TABLENAME , RESEED)

    Reply

Leave a Reply

Menu