How to Create an Identity Column in SQL Server

In this SQL Server tutorial, I will show you how to create an identity column in SQL Server.

As a beginner, you must know how to insert unique records in a table and avoid duplicating records from inserting.

So, here, I will explain a clause called IDENTITY that helps insert unique records into a table to ensure data integrity.

Table of Contents

Create an Identity Column in SQL Server

To create an identity column in SQL Server, the IDENTITY() clause is used, the identity column is kind of column which generate unique integer value automatically for each records in a table.

The IDENTITY clause is used for primary keys.

The syntax is given below.

IDENTITY[(seed,increment)]

Where,

seed: It is the value assigned to the table’s first row.
increment: It is the value by which the following value for the second or future rows is decided. This means that if the identity value of the first row is 1, then the next row will have 2 as the identity value, and so on for other rows in the table.

The syntax for creating a table with an identity column is shown below.

CREATE TABLE (
columnName dataype IDENTITY(1,1),
...
);

In the above syntax, columnName datatype IDENTITY(1,1): The columnName will be the identity column and primary key.

For example, you have to create a table named Orders with an identity column named orderID; here, while specifying the orderID column, you can use the IDENTITY clause as shown below.

CREATE TABLE ORDERS (
    orderID INT IDENTITY(1,1) PRIMARY KEY,
    orderDate DATETIME
);

After creating an Orders table, insert a single record.

INSERT INTO ORDERS (orderDate) VALUES ('2024-01-01');
SELECT * FROM ORDERS;

Look at the output. When a new record is inserted into the ORDERS table, an orderID of 1 is automatically created. This is because of the IDENTITY clause.

The identity value of the first record or row is 1. This is the value in the orderID column that you see in the above output. But how is it decided that the first identity value of the first row will be 1? As you already know, while defining the orderID column, you specified IDENTITY(1,1).

Here, IDENTITY(1,1) means the identity value of the first row in the table will be 1, and each future record’s identity value will increment by 1.

This means that if the second record is inserted into the table, the identity value will be 2; for example, execute the below query to insert the second record.

INSERT INTO ORDERS (orderDate) VALUES ('2024-01-02');
SELECT * FROM ORDERS;

Create an Identity Column in SQL Server and Inserting Second Records

Now look at the orderID column. The value for the second record is 2; in the same way, if you insert the third record, the identity value will be 3.

Let’s insert multiple records.

INSERT INTO ORDERS (orderDate) VALUES ('2024-04-03');
INSERT INTO ORDERS (orderDate) VALUES ('2024-04-04');
INSERT INTO ORDERS (orderDate) VALUES ('2024-04-05');
INSERT INTO ORDERS (orderDate) VALUES ('2024-04-06');
INSERT INTO ORDERS (orderDate) VALUES ('2024-04-07');
INSERT INTO ORDERS (orderDate) VALUES ('2024-04-08');
INSERT INTO ORDERS (orderDate) VALUES ('2024-01-09');
INSERT INTO ORDERS (orderDate) VALUES ('2024-04-10');
SELECT * FROM ORDERS;

Create an Identity Column in SQL Server and Inserting Multiple Records

Again, for multiple records, the identity value is unique. Let’s see how the identity value is assessed when the seed and increment differ.

Create an Identity Column in SQL Server with Different Seed and Increment value

Let’s see what happens when the seed and increment value of the IDENTITY clause is different.

For example, create a new table, Sales, with columns SalesID and SaleDate, as shown below.

CREATE TABLE Sales (
    saleID INT IDENTITY(2,3) PRIMARY KEY,
    saleDate DATETIME
);

In the above table, the sale is defined as an identity column using IDENTITY(2,3), where the identity value of the first row will be 2, and the identity value of the next row will be based on the identity value of the previous row + 3.

For example, let’s insert the first row in the Sales table.

INSERT INTO Sales (saleDate) VALUES ('2024-05-01');
SELECT * FROM Sales;

Create an Identity Column in SQL Server with Different Seed and Increment value

From the output, the identity value of the first record is 2 because the value seed is 2 in the IDENTITY(2,3) clause.

If you insert the second record, the identity value will be 5 because it takes the identity value of the previous row, which is 2 and adds 3 to it because the increment value is 3 in the IDENTITY(2,3) clause.

To verify that, insert the second record.

Identity Column in SQL Server with Different Seed and Increment value

The identity value of the second record is 5, which you can see in the output. In the same way, if another record is, it takes the identity value of the previous (second record) and adds 3 to it to decide the identity value of the other record.

This is how to create an identity column in SQL Server to assign unique integer values to each record in a table.

But why use an identity column?:

Using the identity column, you can easily assign each row a unique value, which is essential for data retrieval and integrity.
Also, it automatically handles the insertion of unique records in the table. Thus, it reduces the error that may arise when the records are inserted manually.
It also helps performance by providing faster indexing and retrieval operations, thus improving query performance.
Remember, the identity column’s data type must be INT for the small table and BIGINT for the large table. Additionally, a situation may arise where you must delete the records; in that case, after deleting the rows, you will have to reset the identity values.

You can reset the identity values using the ‘DBCC CHCEKIDENT’ command.

Conclusion

In this SQL Server tutorial, you learned how to create an identity column in SQL Server.

You learned about clause IDENTITY, which accepts two values: seed and increment. Seed is the starting value for the identity column, and increment increases the identity value for each new row or record.

Additionally, you learned why you should use the identity column for different reasons or benefits.