Microsoft – SQL Server – Transact SQL – Identity Columns – Last Assigned Value


As we prepare to move a database to a new environment, I needed a way to see what shape the database is in.

One of the things I wanted to check are the tables that have Identity Columns.

I basically wanted to see their uptake.



Here is a code that gets the last used Identity value for each table that has an identity Column.


    IDENT_CURRENT (Transact-SQL)

    DBCC Check Ident


			= quotename(schema_name(tblO.schema_id))
              + '.' 
              + quotename(object_name(tblO.object_id))

        , columName
			= tblC.[name]

        , [identityCurrent]
				  + '.' 
				  + quotename

        , [lastIdentityValueAssigned]
			= tblIC.last_value

FROM sys.objects tblO

inner join sys.columns tblC

    ON tblO.object_id = tblC.object_id

    and  tblC.is_identity=1

inner join sys.identity_columns tblIC

    on tblO.object_id = tblIC.object_id

AND   tblO.[type] in (N'U')

order by 
		[identityCurrent] desc



One always wishes Life was awash with easy pickings.  But, even a code like this betrays a few simple assumptions.

Let us see if we can identify a few:

  • If we issue truncate table requests, the system automatically resets this value
  • Not so when we issue deletes.  Even when all records were removed due to an unfiltered deletes, the code branch for deletes does not go through the problem of checking that the table is now empty and have the the identity value re-calibrated


Here is a code from ( ) that gets the current max value from a table and resets the identity value:

 delete from dbo.tblIdentity;

    DECLARE @max_seed BIGINT
    set @max_seed = ISNULL(
                              SELECT MAX(ID) 
                              FROM [dbo].[tblIdentity]

    -- use the current max as the seed
    DBCC CHECKIDENT('[dbo].[tblIdentity]', RESEED, @max_seed)



The code above merely tracks the last Assigned identity value and not quite the value that will be assigned when a new entry is created.

The SQL Server instance possibly considers other things when assigning a new value, but is easier to determine and expose last used values.



Leave a Reply

Please log in using one of these methods to post your comment: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s