When you use T-SQL to create (or alter) a table in SQL Server, you have the option of using the IDENTITY() property to create an identity column.

An identity column contains an automatically incrementing identification number. The IDENTITY() property can be used with the CREATE TABLE and ALTER TABLE statements.

Syntax

The syntax goes like this:

IDENTITY [ (seed , increment) ]

The seed is the value that is used for the very first row loaded into the table.

The increment is the incremental value that is added to the identity value of the previous row that was loaded.

You must specify both arguments or neither. If you specify neither, the default is (1,1).

Example 1 – Basic Usage

Here’s an example to demonstrate.

CREATE TABLE Pets (
    PetId int IDENTITY(1,1) PRIMARY KEY, 
    PetName varchar(255)
    );

This code creates a table with two columns. The first column (PetId) is an identity column because I use the IDENTITY() property in its definition.

Now that I’ve created an identity column, I can insert pet names into the PetName column without needing to include an ID for each row.

INSERT INTO Pets (PetName)
VALUES ('Homer'), ('Felix'), ('Ruff');

SELECT * FROM Pet;

Result:

+---------+-----------+
| PetId   | PetName   |
|---------+-----------|
| 1       | Homer     |
| 2       | Felix     |
| 3       | Ruff      |
+---------+-----------+

Example 2 – Different Seed and Increment

Here’s an example that uses a different seed and increment.

In this case the seed starts at 150 and increments by 10 for each row.

CREATE TABLE Cities (
    CityId int IDENTITY(150, 10) PRIMARY KEY, 
    CityName varchar(255)
    );

Now insert some data and select it:

INSERT INTO Cities (CityName)
VALUES ('Auckland'), ('Danang'), ('Khon Kaen');

SELECT * FROM Cities;

Result:

+----------+------------+
| CityId   | CityName   |
|----------+------------|
| 150      | Auckland   |
| 160      | Danang     |
| 170      | Khon Kaen  |
+----------+------------+

Example 3 – Incorrect Usage

As mentioned, you must specify both arguments or neither. In other words, you can’t provide just one argument. Here’s an example of what happens when you only provide one argument.

CREATE TABLE Cities (
    CityId int IDENTITY(1) PRIMARY KEY, 
    CityName varchar(255)
    );

Result:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.

Example 4 – Trying to Override the Identity Values

If you try to override the automatic identity values, you might get an error like this.

INSERT INTO Cities (CityId, CityName)
VALUES (123, 'Bangkok');

Result:

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Cities' when IDENTITY_INSERT is set to OFF.

This is because you’re trying to override the values created by the IDENTITY() property. In many cases it could be that you just didn’t know it was an identity column. In such cases, all you need to do is remove the identity column from your INSERT statement.

However, if you really do need to override the IDENTITY() property, see How to Insert Values into an IDENTITY Column in SQL Server.

As mentioned, if you don’t need to override the identity values, you can simply remove that column from your INSERT statement.

So I can modify the previous statement to this:

INSERT INTO Cities (CityName)
VALUES ('Bangkok');

SELECT * FROM Cities;

Result:

+----------+------------+
| CityId   | CityName   |
|----------+------------|
| 150      | Auckland   |
| 160      | Danang     |
| 170      | Khon Kaen  |
| 180      | Bangkok    |
+----------+------------+

Limitations of the IDENTITY() Property

The identity property on a column guarantees the following:

• Each new value is generated based on the current seed & increment.
• Each new value for a particular transaction is different from other concurrent transactions on the table.

It does not guarantee the following:

• Uniqueness of the value
• Consecutive values within a transaction
• Consecutive values after server restart or other failures
• Reuse of values (for example, when an INSERT operation fails)

For more information on these limitations, see the Microsoft documentation.