Tag Archives: #Identity

A study on Identity columns in Microsoft SQL Server and tips on how Identity columns can be used to implement business requirements.

#0267 – SQL Server – IDENTITY columns – Myths – IDENTITY columns cannot have holes or “gaps”


Whenever a pattern evolves into a frequently used practice, it does not take long for myths to start getting associated with it. IDENTITY columns have become such a common pattern that often I hear about these myths from novice developers. Using this series, I hope to bust those myths once and for all.


Busting the Myth – IDENTITY columns cannot have holes


Whenever a break in a series is encountered, it is referred to as a hole. For example, if we have a series of numbers from 1 to 5 such that the number 2 is missing (1, 3, 4, 5), then we have a hole.


I often hear from developers that “Holes cannot be generated in the values of a column defined as the IDENTITY column” is a prime reason why one uses IDENTITY columns. This is in-fact incorrect as I will be proving by this post.


The Test


To provide that holes can be generated in an IDENTITY column, I will use the script shown below. The script:



  1. Prepares the environment by creating a table and adding some test data into it

  2. Check the current identity value of the table using IDENT_CURRENT()

  3. Opens a transaction

  4. Performs an insert into a table which has the IDENTITY property defined

  5. Later, the transaction is rolled back, and the current identity value of the table is queried using IDENT_CURRENT()

  6. To confirm, a record is inserted into the table and the values selected – clearly indicating the presence of the hole

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY(1,1),
                            IdentityValue VARCHAR(10)
                           );
GO

--Add some test data
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('One'),
       ('Two'),
       ('Three');
GO

--2. Check the current identity value of the table using IDENT_CURRENT()
SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueBeforeTest;
GO

--3. Perform the test
--3a. Open a transaction
BEGIN TRANSACTION IdentityHolesCheck
    --3b. Perform an insert
    INSERT INTO dbo.tIdentity (IdentityValue)
    VALUES ('Four');
--3c. Rollback the transaction
ROLLBACK TRANSACTION IdentityHolesCheck;
GO

--4. Check the current identity value of the table using IDENT_CURRENT()
SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterTest;
GO

--5. Confirmatory test
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('Five');
GO

--6. Select the values from the table
SELECT ti.IdentityId, ti.IdentityValue
FROM dbo.tIdentity AS ti;
GO

--Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
IdentityValueBeforeTest
-----------------------
3

IdentityValueAfterTest
----------------------
4

IdentityId  IdentityValue
----------- -------------
1           One
2           Two
3           Three
5           Five
*/

While this was a simulation, in a practical world this situation is similar to a failed insert. Even if the INSERT did not go through, the IDNETITY value was incremented leaving the hole behind.


Conclusion


The test shown above clearly proves that it is possible to have holes in an IDENTITY column. Any code that expects an uninterrupted series of values may break if this has not been taken into consideration.


Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

#0266 – SQL Server – IDENTITY Columns – @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT and $IDENTITY


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.