SQL Server Identity Columns

SQL Server IDENTITY columns are numeric columns which are set to auto increment when new values are inserted into the table. These columns are great for uniquely identifying records in a table, especially when you do not have any natural key to define a primary key constraint on the table.  Like a primary key, there can only be one IDENTITY property defined on a column in a table.

An IDENTITY column has a seed value (where the values begin) and the numeric increment value for each new record inserted. When new values are inserted into the table, the new valued will be based on the increment value of the identity column. The first value inserted into the table will have the value of seed value as specified.

The following data types can have the IDENTITY property set for them: decimal, int, numeric, smallint, bigint, and tinyint.

One potential issue you may run into when using IDENTITY columns is when you are merging data from two or more tables into one common table. Because the tables which contain the merged IDENTITY columns may overlap values, it is important to define what may be required up front so this is avoided. For example, if you have a table from SystemA and a table from SystemB which will be combined and used in SystemC, it is critical that the IDENTITY values from the twosystems are defined so that they do not overlap when they are combined in SystemC.    

 CREATE TABLE Articles
(
    ArticleID INT IDENTITY(1,1),
    Title VARCHAR(255) NOT NULL,
    AuthorID INT NOT NULL,
    CreationDate SMALLDATETIME,
    CONSTRAINT pk_Articles_ArticleID PRIMARY KEY CLUSTERED(ArticleID ASC)
) 

INSERT INTO Articles
(
    Title, AuthorID, CreationDate
)
SELECT 'Identity Columns', 1, GETDATE()

SELECT SCOPE_IDENTITY(), @@IDENTITY 

It is easy to determine the current identity increment value for a table which contains an IDENTITY column. The IDENT_CURRENT returns the current IDENTITY increment value for a specific table.

SELECT IDENT_CURRENT('Articles') 

I can also use a DBCC command to return the current increment value for an IDENTITY column.

DBCC CHECKIDENT ('Articles') 

SELECT OBJECTPROPERTY (OBJECT_ID('Articles'),'TableHasIdentity')

 SELECT
      CASE WHEN EXISTS 
        ( 
            SELECT
                1
            FROM
                sys.tables t
                JOIN sys.columns c on t.object_id = c.object_id
            WHERE
                t.name = 'Articles'
                AND c.is_identity = 1 
        ) THEN 1
        ELSE 0
    END 

IDENTITY Columns in Transactions

One interesting thing about IDENTITY columns are that when INSERT statements are rolled back, the current increment value of the IDENTITY column is NOT rolled back. Each execution of the following script increments the IDENTITY value, even though the actual operation is not committed.

BEGIN TRANSACTION

INSERT INTO Articles
(
    Title, AuthorID, CreationDate
)
SELECT 'Identity Columns', 1, GETDATE()

SELECT SCOPE_IDENTITY(), @@IDENTITY
ROLLBACK TRANSACTION 

I can check the current increment value of the IDENITITY column value on the Articles table using the IDENT_CURRENT function.

SELECT IDENT_CURRENT('Articles') 

If I want to reset the increment value for the table, I can use the DBCC CHECKIDENT command to reseed the increment value. I use 1 as the reseed value because that was the last value I inserted into the table. The next value inserted will be 2.

DBCC CHECKIDENT ('Articles', 'RESEED', 1) 

In the above INSERT examples, I returned the values from @@IDENTITY and SCOPE_IDENTITY() to view the last IDENTITY value inserted. So, what is the difference between them? Well, the answer is the scope in which the INSERT occurs. In the above example, both values are the same because the INSERT action only occurs on one table. However, if the Articles table had a trigger on it, and that trigger inserts a new value into a table which also has an IDENTITY column, the values can be different. In the following example an article table is defined for the Articles table and a a trigger is defined to archive inserted or updated records.

CREATE TABLE ArticlesArchive
(
    ArchiveID INT IDENTITY(1000, 1),
    ArticleID INT,
    Title VARCHAR(255) NOT NULL,
    AuthorID INT NOT NULL,
    CreationDate SMALLDATETIME,
    ArchiveDate SMALLDATETIME 
    CONSTRAINT pk_ArticlesArchive_ArchiveID PRIMARY KEY CLUSTERED (ArchiveID ASC)
)

CREATE TRIGGER tr_Articles_ArchiveData ON Articles
AFTER INSERT, UPDATE
AS
BEGIN

    INSERT INTO    ArticlesArchive
    (
        ArticleID,
        Title,
        AuthorID,
        CreationDate,
        ArchiveDate
    )
    SELECT
        ArticleID,
        Title,
        AuthorID,
        CreationDate,
        GETDATE()
    FROM
        INSERTED
END

Now, if the same INSERT statement from above is ran, the outcome is drastically different.

INSERT INTO Articles
(
    Title, AuthorID, CreationDate
)
SELECT 'Identity Columns 2', 1, GETDATE()

SELECT SCOPE_IDENTITY(), @@IDENTITY 

CREATE TABLE #IdentityOutput (ArticleID INT)

INSERT INTO Articles
(
    Title,
    AuthorID,
    CreationDate
)
OUTPUT
    INSERTED.ArticleID
INTO #IdentityOutput (ArticleID)
SELECT
    'Temp Tables',
    4,
    GETDATE()
UNION ALL
SELECT
    'Views',
    5,
    GETDATE()
UNION ALL
SELECT
    'Stored Procedures',
    6,
    GETDATE()
UNION ALL
SELECT
    'Constraints',
    7,
    GETDATE()
UNION ALL
SELECT
    'Database Mail',
    8,
    GETDATE()

SELECT *
FROM #IdentityOutput 

Using the OUTPUT method, you can easily capture all new IDENTITY column values and then use them in subsequent operations.  This allows you to get away from the iterative IDENTITY capture approach and from having to query the table right after you have inserted the new values. 

Wrap Up

Today I looked at the different methods you can use to capture IDENTITY values after they've been inserted into a SQL Server table.  Capturing these values is crucial to many applications, and the more efficiently you can capture the values, the better.