IDENTITY columns are perhaps the least understood and yet most used features of Microsoft SQL Server.

In this post, I will explore some system functions and keywords that we as developers and administrators can use to get information about IDENTITY columns.

Whenever a record is inserted to a table using an IDENTITY column via an INSERT, SELECT INTO or bulk copy command, the identity values on the affected tables are incremented. Microsoft SQL Server gives us three system functions that can provide the last used identity value – depending upon the scope and session.

IDENT_CURRENT()

Given a table name, this system function returns the last identity value used for the given table or view. Since this function works on a particular table or view, this value is independent of the session or the scope.

SCOPE_IDENTITY()

This system function provides the last used IDENTITY value within the same scope.

Statements inside the same stored procedure, trigger or batch comprise of the same scope – those within triggers fired as a result of a statement inside a stored procedure aren’t.

If no statement has been executed which affects an IDENTITY value, this function returns NULL.

@@IDENTITY

The @@IDENTITY system function contains the last value that was generated by the statement – irrespective of the scope.

If no statement has been executed which affects an IDENTITY value, this function returns NULL. However, please note that this function reports the identity value last generated by the statement – irrespective of the scope. This means that if a statement causes execution of a trigger which generates identity values in other tables, calling the @@IDENTITY function immediately after this statement will return the last IDENTITY value generated by the trigger.

Let’s look at each of these via an example:

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON ;

SELECT  IDENT_CURRENT('Purchasing.PurchaseOrderDetail') AS PODetailIdentity,
        IDENT_CURRENT('Production.TransactionHistory') AS TransactionlIdentity,
        SCOPE_IDENTITY() AS ScopeIdentityValue,
        @@IDENTITY AS LastUsedIdentity;

    --Perform the INSERT into the table: Purchasing.PurchaseOrderDetail
    --The insert trigger on this table: Purchasing.iPurchaseOrderDetail, causes a record
    --to be inserted into Production.TransactionHistory, thereby incrementing it's IDENTITY column - TransactionID
    INSERT  INTO Purchasing.PurchaseOrderDetail
            (
              PurchaseOrderID,
              DueDate,
              OrderQty,
              ProductID,
              UnitPrice,
             --LineTotal,
              ReceivedQty,
              RejectedQty,
             --StockedQty,
              ModifiedDate
            )
            SELECT  1 AS PurchaseOrderId,
                    '2005-05-31' AS DueDate,
                    1 AS OrderQty,
                    4 AS ProductId,
                    57.02 AS UnitPrice,
                    --258.06 AS LineTotal,
                    1 AS ReceivedQty,
                    0 AS RejectedQty,
                    --1 AS StockedQty,
                    '2013-05-09' AS ModifedDate ;
    GO

SELECT  IDENT_CURRENT('Purchasing.PurchaseOrderDetail') AS PODetailIdentity,
        IDENT_CURRENT('Production.TransactionHistory') AS TransactionlIdentity,
        SCOPE_IDENTITY() AS ScopeIdentityValue,
        @@IDENTITY AS LastUsedIdentity;

/****RESULTS****/
/*
        PODetailIdentity    TransactionlIdentity    ScopeIdentityValue    LastUsedIdentity
Before: 8847                213450                  NULL                  NULL  
After : 8848                213451                  8848                  213451
*/

Inserting a new record in the table: Purchasing.PurchaseOrderDetail causes the insert trigger on this table: Purchasing.iPurchaseOrderDetail to be executed. This trigger maintains a history of the PurchaseOrder Detail transactions in the table – Production.TransactionHistory, thereby incrementing it’s IDENTITY column – TransactionID.

As can be seen in the output:

• IDENT_CURRENT() provides us the last used identity value for the given table


• SCOPE_IDENTITY() provides us the last used identity value within the scope of the batch


• @@IDENTITY provides us the last used identity value irrespective of the scope

$IDENTITY

The $IDENTITY keyword allows us to refer to an identity column in a table without using the corresponding column name. Here’s an example:

USE AdventureWorks2008R2 ;
GO
SELECT  $IDENTITY
FROM    Purchasing.PurchaseOrderDetail ;
GO

The query above will return the value of the [PurchaseOrderDetailId] in the table [Purchasing].[PurchaseOrderDetail] since it’s marked as an identity column.

Other functions – IDENT_INCR() and IDENT_SEED()

If one needs to look at the value of the seed and increment values used in the identity column definition, the IDENT_INCR() and IDENT_SEED() functions can be used.

USE AdventureWorks2008R2;
GO
SELECT IDENT_SEED('Production.TransactionHistory') AS IdentityIncrement,
       IDENT_INCR('Production.TransactionHistory') AS IdentitySeed;
GO
/* RESULTS */
/*
IdentityIncrement   IdentitySeed
100000              1
*/

Checking for Identity columns in a table

The Catalog View sys.identity_columns has one record for each identity column and provides information like the seed, increment, last value generated for that column and other meta-data information.

Usage is shown in the query below:

USE AdventureWorks2008R2 ;
GO
SELECT * FROM sys.identity_columns
WHERE object_id = OBJECT_ID('Production.TransactionHistory');
GO

Until we meet next time,

Be courteous. Drive responsibly.