SQL Server Create Table With Identity Column

by

In this guide, I am going to walk you through everything you need to know to create a table with an identity column in SQL Server. I will share the syntax and the best practices I use today to ensure data integrity.

SQL Server Create Table With Identity Column

What is an Identity Column in SQL Server?

Before we start writing code, let’s define exactly what we are dealing with. An Identity column is a special type of column in a database table that automatically generates a numeric value for each new row inserted.

When I define a column with the IDENTITY property, I am essentially telling SQL Server: “You handle the numbering; I’ll worry about the data.”

Key Characteristics

  • Automatic Generation: You do not (usually) insert values into this column manually.
  • Uniqueness: While not a constraint itself, it is almost always paired with a PRIMARY KEY constraint to guarantee uniqueness.
  • Sequential (Mostly): It typically counts up by one, though we will discuss later why you shouldn’t rely on it being perfectly unbroken.

The Syntax: How to Create a Table with Identity Column

The syntax is straightforward, but it offers more flexibility than most beginners realize. The basic structure relies on the IDENTITY keyword, which accepts two optional arguments: the Seed and the Increment.

The Arguments Explained

ArgumentDefinitionDefault Value
SeedThe value of the very first row loaded into the table.1
IncrementThe amount added to the identity value of the previous row.1

If I write IDENTITY without arguments, SQL Server assumes IDENTITY(1,1).

Step-by-Step Implementation

Let’s look at a practical example. Suppose I need a table to track Employees.

Here is how I would write the T-SQL statement:

SQL

CREATE TABLE dbo.Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NULL,
    HireDate DATE DEFAULT GETDATE()
);

In this script:

  1. EmployeeID is my identity column.
  2. INT is the data type. This is standard, though for massive tables (billions of rows), I would use BIGINT.
  3. IDENTITY(1,1) tells the server to start at 1 and increment by 1 for each new employee.

After executing the above query, I got the expected output as shown in the screenshot below.

SQL Server Create Table With Identity Column

Customizing the Identity: Seed and Increment

Sometimes, the default (1,1) isn’t what I need.I simply adjusted the arguments:

SQL

CREATE TABLE dbo.Invoices (
    InvoiceID INT IDENTITY(1000, 10),
    CustomerName VARCHAR(100),
    TotalAmount DECIMAL(10, 2)
);
  • Seed (1000): The first invoice inserted will have ID 1000.
  • Increment (10): The second invoice will be 1010, the third 1020, and so on.
create table identity column sql server

This approach is also useful when you have multiple sites merging data and you want to avoid collisions (e.g., Site A starts at 1, Site B starts at 1,000,000).

Inserting Data into an Identity Column

This is where I see most confusion occur. Once the table is created, how do we get data in?

The Standard Insertion

Under normal circumstances, you ignore the identity column in your INSERT statement. SQL Server handles it.

SQL

-- Correct usage
INSERT INTO dbo.EmployeesN (FirstName, LastName, Email)
VALUES ('John', 'Doe', 'john.doe@azurelessons.com');

INSERT INTO dbo.EmployeesN (FirstName, LastName, Email)
VALUES ('Emily', 'Smith', 'emily.smith@azurelessons.com');

If I were to query the table now, John Doe would be ID 1, and Emily Smith would be ID 2.

After executing the above query, I got the expected output as shown in the screenshot below.

SQL Create Table With Identity Column

The Error: Trying to Insert Explicit Values

Cannot insert explicit value for identity column in table

Retrieving the Generated Identity Value

After I insert a record, I often need to know the ID that was just generated—perhaps to insert a related record into a Orders or Addresses table.

There are three ways to get this, but only one is safe for high-concurrency environments.

1. @@IDENTITY

This system function returns the last identity value generated in the current session.

  • Risk: If your insert triggers a trigger that inserts into another table, @@IDENTITY will give you the ID from the trigger’s table, not your original table.

2. IDENT_CURRENT('tablename')

This returns the last identity value generated for a specific table, regardless of who did it.

  • Risk: In a busy system, another user in Boston might insert a row milliseconds after you. IDENT_CURRENT would return their ID, not yours.

3. SCOPE_IDENTITY() (The Best Practice)

This is the one I use 99% of the time. It returns the last identity value generated in the current scope (your procedure or batch) and strictly avoids values generated by triggers.

Example Usage:

SQL

DECLARE @NewID INT;

INSERT INTO dbo.EmployeesN (FirstName, LastName)
VALUES ('Sarah', 'Connor');

-- Capture the new ID safely
SET @NewID = SCOPE_IDENTITY();

SELECT @NewID AS [Newly Created ID];

Understanding Identity Gaps

One of the most frequent questions from developers is: “Why are there gaps in my ID sequence? I have ID 4 and ID 6, but where is ID 5?”

It is crucial to understand that the Identity property guarantees uniqueness, not contiguousness. Gaps happen for several reasons:

  1. Rollbacks: If I begin a transaction, insert a row (generating ID 5), and then roll back that transaction, ID 5 is “burned.” The next insert will get ID 6. SQL Server does not “put back” the used number.
  2. Server Restarts: SQL Server caches identity values for performance. If the server crashes or restarts unexpectedly, the unused values in the cache are lost.
  3. Deletions: Obviously, if I delete the row with ID 5, that number is gone forever (unless I manually re-insert it using IDENTITY_INSERT).

My Advice: Never use Identity columns for business logic that requires an unbroken sequence (like Check Numbers or Invoice Numbers). For those, you should handle the numbering application-side or use a custom Sequence object.

Managing Identity: Reseeding and Truncating

Sometimes, during development or testing, I mess up my data and want to start over.

Reseeding with DBCC CHECKIDENT

If I have deleted rows and want the next insert to start at a specific number, I use DBCC CHECKIDENT.

SQL

-- Reset the identity so the next record is 1
DBCC CHECKIDENT ('dbo.Employees', RESEED, 0);

Note: If I reseed to 0, the next insert will be 0 + Increment (1) = 1.

Delete vs. Truncate

There is a massive difference between these two commands regarding identity columns:

  • DELETE FROM dbo.Employees: Removes the data but does not reset the identity counter. If the last ID was 1000, the next one will be 1001.
  • TRUNCATE TABLE dbo.Employees: Removes all data and resets the identity counter back to the original seed.

Data Type Considerations: INT vs. BIGINT

When I create a table with an identity column, I have to choose a data type. This decision is more critical than it looks.

  • INT: The standard integer. It supports values up to 2,147,483,647.
  • BIGINT: The 64-bit integer. It supports values up to 9,223,372,036,854,775,807.
  • SMALLINT/TINYINT: Much smaller ranges (32,767 and 255 respectively).

Summary of Best Practices

To wrap up, here is a checklist of best practices I follow when working with SQL Server identity columns:

  • Always use a Primary Key: Identity columns are almost always Surrogate Keys. Pair them with a Primary Key constraint.
  • Use SCOPE_IDENTITY(): Avoid @@IDENTITY to prevent bugs with triggers.
  • Plan for Growth: Use BIGINT if you anticipate high volume.
  • Don’t Obsess Over Gaps: Educate your stakeholders that gaps are a natural part of database mechanics and do not indicate data loss.
  • Avoid IDENTITY_INSERT in Production: Keep explicit inserts for migration scripts or disaster recovery only.

Conclusion

Creating a table with an identity column in SQL Server is a fundamental skill that underpins the architecture of most relational databases. Whether you are tracking users, the IDENTITY property provides a reliable, high-performance method for generating unique keys.

Now that you have the knowledge, the next step is to open up SQL Server Management Studio and try creating your own table using the examples above.

You may also like the following articles: