Introduction

In SQL Server, identity columns are used to automatically generate unique numbers for new rows in a table. This article will guide you through creating, seeding, and managing identity columns with practical examples.

Creating a Table with an Identity Column

To create a table with an identity column, use the IDENTITY keyword:

CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

This creates an EmployeeID column that starts at 1 and increments by 1.

Inserting Data

You don't need to provide values for the identity column:

INSERT INTO Employees (FirstName, LastName) VALUES ('John', 'Doe');
INSERT INTO Employees (FirstName, LastName) VALUES ('Jane', 'Smith');

The EmployeeID values are automatically generated.

Seeding Identity Values

Use DBCC CHECKIDENT to set the starting value:

DBCC CHECKIDENT ('Employees', RESEED, 1000);

The next inserted row will have EmployeeID 1001:

INSERT INTO Employees (FirstName, LastName) VALUES ('Alice', 'Brown');

Handling Identity Insertions

Enable IDENTITY_INSERT to insert explicit values:

SET IDENTITY_INSERT Employees ON;

INSERT INTO Employees (EmployeeID, FirstName, LastName) VALUES (2000, 'Bob', 'White');

SET IDENTITY_INSERT Employees OFF;

Resetting Identity Seed

Use TRUNCATE TABLE to reset the identity seed:

TRUNCATE TABLE Employees;

This resets the identity value back to the original seed.

Conclusion

By understanding and using identity columns, you can efficiently manage unique identifiers in your SQL Server tables.