Introduction

There are a number of ways to generate key values in SQL Server tables, including the IDENTITY column property, the NEWID() function and more recently, SEQUENCES. The IDENTITY column property is the earliest of these methods. It was introduced very early in the history of SQL Server, and it is arguably the simplest approach. This article is the first in a three-part series that explores methods of generating key values in SQL Server.

IDENTITY (seed, increment)

The IDENTITY column property is specified as part of the CREATE TABLE command or at any time in the life cycle of a table using the ALTER TABLE command. In the latter case, you need to create a new column. The property tells SQL Server to generate values for a column automatically. A new number is automatically generated for each additional row of that column starting at the seed and incrementing at intervals determined by the increment.

Code Sample

The IDENTITY property is a simple solution for cases where you can use integers as key values. Identity works well and is simple to implement but has some drawbacks which we shall highlight later in this article. The typical use of the property is shown in Listing 1.

CREATE DATABASE AUTOROWS
GO
USE AUTOROWS
GO
CREATE TABLE EMPLOYEE
(ID INT IDENTITY (1,1)
, FNAME NVARCHAR (50)
, LNAME NVARCHAR (50)
, HIREDATE DATETIME
);
GO
INSERT INTO EMPLOYEE VALUES ('Amos','Gyekye','20020210');
INSERT INTO EMPLOYEE VALUES ('Agnes','Okigwe','20120110');
INSERT INTO EMPLOYEE VALUES ('Fred','Okoro','20000809');
INSERT INTO EMPLOYEE VALUES ('Harry','Owusu','20090302');
INSERT INTO EMPLOYEE VALUES ('Ernest','Ofori','20020210');
GO
SELECT * FROM EMPLOYEE;
GO

Listing 1: Table Creation Showing IDENTITY Property of an ID Column

The insert results are shown in Fig 1.

Fig. 1: Sample Use Case

Two things are noteworthy when using the IDENTITY property:

1. It is used for a column with numerical data (INT in this case)
2. When inserting rows, it is not necessary to specify values for that column. If you do, the error shown in Fig 2 will be returned

Figure 2: Error Message on Attempt to Populate an IDENTITY Column

Seed and Increment

The choice of values for the seed and increment are completely at the discretion of the DBA. The seed and increment must both be integers. While the seed can be any integer on the number line - positive and negative integers including zero, zero is not a valid value for the increment. In Listing 2 we use a very large BIGINT seed and an increment of 1000 to create the table MANAGER. (See Fig. 3).

USE AUTOROWS
GO
CREATE TABLE MANAGER
(ID BIGINT IDENTITY (2345678493,1000)
, FNAME NVARCHAR (50)
, LNAME NVARCHAR (50)
, HIREDATE DATETIME
);
GO
INSERT INTO MANAGER VALUES ('Amos','Gyekye','20020210');
INSERT INTO MANAGER VALUES ('Agnes','Okigwe','20120110');
INSERT INTO MANAGER VALUES ('Fred','Okoro','20000809');
INSERT INTO MANAGER VALUES ('Harry','Owusu','20090302');
INSERT INTO MANAGER VALUES ('Ernest','Ofori','20020210');
GO
SELECT * FROM MANAGER;
GO

Listing 2: Table Showing IDENITITY Property of an ID BIGINT Column using Large Increment

Figure 3: Sample Use Case of IDENTITY Property with BIGINT Seed

IDENTITY_INSERT

One key characteristic of an IDENTITY column is that SQL Server does not allow manual inserts by default. In order to manually insert rows in a table with such a column, you need to

1. Set IDENTITY_INSERT ON
2. Ensure all columns matching the values being inserted are listed in the insert statement (See Listing 3).

SET IDENTITY_INSERT EMPLOYEE ON;
GO 
INSERT INTO EMPLOYEE 
(ID, FNAME, LNAME, HIREDATE)
VALUES 
(6,'Ernest','Ofori','20020210');
GO
SET IDENTITY_INSERT EMPLOYEE OFF;
GO

Listing 3: Setting IDENTITY_INSERT ON

It is also important to know that you cannot set IDENTITY_INSERT ON for two tables in the same session (See Fig. 4). We discuss this further in the next section. When dealing with tables having an identity column, consider turning IDENTITY_INSERT ON as a temporary action. Be sure to revert to the normal by issuing SET IDENTITY_INSERT .<Table_Name> OFF when done with manual inserts.

Figure 4: Error Message on Attempt to Turn IDENTITY_INSERT ON Twice in the Same Session

A Little More About IDENTITY Columns

A question that would come up in a young one’s mind is why there is a restriction on setting IDENTITY_INSERT ON to one table per session. A simple way of looking at it is that we would want to prevent accidental manual inserts in the IDENTITY column. If we keep having to insert values manually then there is no need to use the property in the first place. The fact that you can manually insert duplicate values in the IDENTITY column when IDENTITY_INSERT is ON exacerbates this concern  (See Figures 5 and 6). This obviously compromises the purpose of an ID column. Also note that this means that you cannot use this property to guarantee uniqueness in a column.

Figure 5: Insert Duplicate IDs (a)

Figure 6: Insert Duplicate IDs (b)

Another interesting caveat is that we can actually introduce ‘holes’ in the sequence by deleting rows (See Listing 4. In Figure 7, ID 8 is the highest employee identifier but we cannot say that we have 8 employees. In other words, when dealing with tables where this property has been enabled and IDENTITY_INSERT is turned on, do not assume the largest value of the column of interest in any way indicates the number of rows in the table.

USE AUTOROWS
GO
DELETE FROM EMPLOYEE WHERE ID IN (3,4,6);
GO
SELECT * FROM EMPLOYEE;
GO

Listing 4: Delete Rows from an IDENTITY Table

Figure 7: Deleting Rows from an IDENTITY Table

IDENTITY-Related Functions

The @@IDENTITY system function returns the last inserted value in a session. Listing 5 shows an example. One would typically use this function in scenarios where one needs to quickly pass the latest value to another section of one's code.

INSERT INTO EMPLOYEE 
(FNAME, LNAME, HIREDATE)
VALUES 
('Kevin','Townsend','20080210');
GO
select @@IDENTITY AS LASTID
SELECT * FROM EMPLOYEE;
GO
PRINT 'The ID of the Last Staff Employed is ' + CAST(@@IDENTITY AS VARCHAR);

Listing 5: Checking the Last IDENTITY Value

The function SCOPE_IDENTITY works in the same way as @@IDENTITY however you would use it within a given scope: a procedure, function, trigger or batch. This limitation to a scope becomes more important when dealing with a large complex systems where transactions are happening concurrently. Figure 8 shows a comparison of the outputs of these functions.

While the limitation in scope of SCOPE_IDENTITY might sound like a bad thing, it is actually a good thing! @@IDENTITY is known to return the "wrong" last identity value in cases where an insert in one table invokes a trigger that inserts into another table with an IDENTITY column (See number 7 in the reference list). @@IDENTITY also has concurrency issues. SCOPE_IDENTITY() also has its own problems with certain statements that require execution plans involving parallelism.

USE AUTOROWS
GO
SELECT @@IDENTITY AS [Current Session Identity]
, SCOPE_IDENTITY() AS [Scope Identity]
, IDENT_CURRENT('EMPLOYEE') [Employee Current Identity]
, IDENT_CURRENT('MANAGER') [Manager Current Identity];
GO

Listing 6: IDENTITY-Related Functions

Figure 8: IDENTITY Related Functions

Conclusion

In this article, we have explored the use of the IDENTITY column property as a way to generate key values in a table. As mentioned earlier, Microsoft has since introduced more efficient methods of generating key values such as the NEW() function and sequences. You will find IDENTITY an easily to implement option in small projects involving SQL Server. In subsequent articles, we shall explore the use of more modern approaches to generating key values.

References

You can find more information about the IDENTITY column property from the following resources:

1. Create Table IDENTITY Property
2. SET IDENTITY_INSERT
3. IDENT_CURRENT
4. SCOPE_IDENTITY OR @@IDENTITY
5. Easy Way to Reseed an Identity Column
6. SQL Sequence vs IDENTITY Column
7. Why You Should Avoid Using the Identity Function