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

Background

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.

 

Code

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

 

/*
    IDENT_CURRENT (Transact-SQL)
        http://msdn.microsoft.com/en-us/library/ms175098.aspx

    DBCC Check Ident
        http://msdn.microsoft.com/en-us/library/ms176057.aspx

*/
SELECT 

          quotename(schema_name(tblO.schema_id))
                             + '.' 
                             + quotename(object_name(tblO.object_id))
            as objectName                                

        , tblC.name AS columName

        , IDENT_CURRENT(quotename(schema_name(tblO.schema_id))
                             + '.' 
                             + quotename(object_name(tblO.object_id))
                       ) as identityCurrent

        , tblIC.last_value as lastIdentityValueAssigned

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 3 desc

 

Opportunities

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 SQLServerPlanet.com ( http://sqlserverplanet.com/dba/using-dbcc-checkident-to-reseed-a-table-after-delete ) 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]
                            )
                            ,0)

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

 

Summary

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:

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