Logo
Back

How do I auto increment the primary key in a SQL Server database table?

Auto-incrementing a primary key in SQL Server is typically done by defining an identity column. An identity column automatically generates sequential numbers for each new row inserted into the table. Below is a step-by-step guide on how to set this up.

1. Creating a Table with an Identity Column

You can create a table with an identity column by specifying the IDENTITY property on the primary key column. For example:

CREATE TABLE Employees (
    EmployeeID INT NOT NULL IDENTITY(1,1), 
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
);
  • EmployeeID INT NOT NULL IDENTITY(1,1):
    • INT specifies the data type of the column.
    • NOT NULL ensures the column must always contain a value.
    • IDENTITY(1,1) indicates that auto-increment starts at 1 and increments by 1 for each new row.
  • Primary Key Constraint: The CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID) line sets EmployeeID as the primary key.

2. Inserting Rows

Once you have an identity column, you don’t have to specify values for the primary key when inserting rows:

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

SELECT * FROM Employees;

The EmployeeID column will be auto-populated with sequential values.

3. Customizing the Identity

When defining the identity column, you can change the initial seed and increment:

CREATE TABLE Products (
    ProductID INT NOT NULL IDENTITY(100,5),
    ProductName VARCHAR(100) NOT NULL,
    CONSTRAINT PK_Products PRIMARY KEY (ProductID)
);
  • IDENTITY(100,5) means:
    • The first inserted row has a value of 100 in the ProductID column.
    • Each subsequent row increments by 5 (i.e., 105, 110, etc.).

4. Dealing with Reseeding

If you need to reset the identity value after deleting data, you can use DBCC CHECKIDENT:

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

This command resets the next identity value for the specified table to 1 (or whatever value you choose).

5. Best Practices

  1. Avoid Updating Identity Values: Identity columns are meant to be unique record identifiers. Changing them can break relationships with other tables.
  2. Don’t Expose as a Meaningful Business Key: Identity columns should remain surrogate keys, not represent domain-specific IDs (like invoice numbers).
  3. Use Transactions: If your table is central to critical data, wrap inserts in a transaction. Although identity assignments are usually robust, transactions provide an extra layer of safety.

Strengthen Your SQL Expertise with DesignGurus.io

  1. Grokking SQL for Tech Interviews – Dive into essential SQL concepts, best practices, and practical coding patterns often tested in interviews.
  2. Grokking Database Fundamentals for Tech Interviews – Learn about normalization, indexing, and how to optimize database design for large-scale systems.

You can also explore the DesignGurus.io YouTube channel for free tutorials on advanced SQL and system design. If you’re gearing up for a major technical interview, their Mock Interviews offer real-time feedback from ex-FAANG engineers.

Conclusion

To auto-increment a primary key in SQL Server, define the column with the IDENTITY property in your CREATE TABLE statement. This automatically assigns sequential values to the column, eliminating the need to manually specify IDs for each insert. By following best practices and understanding how to manage identity reseeding, you can maintain a clean and efficient approach to primary key generation.

CONTRIBUTOR
TechGrind