When working with SQL Server tables, there are several methods available to generate key values. In this article, we will explore one of the earliest and simplest approaches – the IDENTITY column property.

The IDENTITY column property is specified as part of the CREATE TABLE command or can be added to an existing table using the ALTER TABLE command. This property instructs SQL Server to automatically generate values for a specific column. Each new row inserted into the table will be assigned a unique number, starting from a specified seed value and incrementing at intervals determined by the increment value.

Let’s take a look at an example:

CREATE TABLE Employees
(
    ID INT IDENTITY(1, 1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    HireDate DATETIME
);

INSERT INTO Employees (FirstName, LastName, HireDate)
VALUES ('John', 'Doe', '2020-01-01');

SELECT * FROM Employees;

In the above example, we create a table called “Employees” with an ID column defined as an IDENTITY column. When we insert a new row into the table without specifying a value for the ID column, SQL Server automatically generates a unique ID for that row. We can then retrieve all the rows from the table using the SELECT statement.

One important thing to note is that the IDENTITY property can only be used for columns with numerical data types, such as INT or BIGINT. It is not necessary to specify a value for the IDENTITY column when inserting rows, as SQL Server will handle the generation of unique values automatically.

However, if you need to manually insert rows into a table with an IDENTITY column, you can use the IDENTITY_INSERT option. This option allows you to temporarily enable manual inserts for a specific table. Here’s an example:

SET IDENTITY_INSERT Employees ON;

INSERT INTO Employees (ID, FirstName, LastName, HireDate)
VALUES (100, 'Jane', 'Smith', '2020-02-01');

SET IDENTITY_INSERT Employees OFF;

By setting IDENTITY_INSERT ON, we can manually insert a row into the Employees table and specify a value for the ID column. Once we’re done with the manual inserts, we can turn off the IDENTITY_INSERT option.

It’s important to note that you can only have IDENTITY_INSERT enabled for one table per session. This restriction is in place to prevent accidental manual inserts in the IDENTITY column, as it defeats the purpose of using the IDENTITY property to generate unique values.

Another consideration when working with IDENTITY columns is that deleting rows from a table can create “holes” in the sequence of generated values. For example:

DELETE FROM Employees WHERE ID = 2;

SELECT * FROM Employees;

In the above example, we delete a row with ID 2 from the Employees table. As a result, there is now a gap in the sequence of ID values. It’s important to keep this in mind when using IDENTITY columns to ensure that the largest value of the column does not indicate the number of rows in the table.

SQL Server provides several functions related to IDENTITY columns, such as @@IDENTITY and SCOPE_IDENTITY. These functions allow you to retrieve the last inserted value in a session or within a specific scope, respectively.

In conclusion, the IDENTITY column property is a simple and easy-to-implement method for generating key values in SQL Server tables. While there are more efficient methods available, such as the NEWID() function and sequences, the IDENTITY property is still a viable option for small projects or when simplicity is preferred.

References:

• Create Table IDENTITY Property
• SET IDENTITY_INSERT
• IDENT_CURRENT
• SCOPE_IDENTITY
• Easy Way to Reseed an Identity Column
• SQL Sequence vs IDENTITY Column
• Why You Should Avoid Using the Identity Function