SQL Server Identity Column

In SQL Server, a column in a table can be set as an identity column. It is used for generating key values for primary key columns.

Use the IDENTITY[(seed, increment)] property with the column to declare it as an identity column in the CREATE TABLE or ALTER TABLE statements.

column_name data_type IDENTITY[(seed, increment)]

Parameters:

1. Seed is the first value of the identity column.
2. Increment is the incremental value added to the identity value of the previous row.

The identity property generates a new value based on the current seed & increment. Each new value for a particular transaction is different from other concurrent transactions on the table.

The identity property does not guarantee the uniqueness of the value. You must make a column as a PRIMARY KEY or UNIQUE constraint or UNIQUE index to make it unique.

You can define only one column per table as the identity column.

Declare IDENTITY column

The following CREATE TABLE statement declares the EmpID column as the identity column.

CREATE TABLE Employee(	[EmpID] [int] IDENTITY(1,1) NOT NULL,	[FirstName] [nvarchar](50) NOT NULL,	[LastName] [nvarchar](50) NOT NULL);

In the above CREATE TABLE SQL statement, the EmpID is the IDENTITY column with seed and increment at 1. So, whenever a new row is inserted, the EmpID will be incremented by 1. If you don't specify any parameters then the default is IDENTITY(1,1).

You can also set identity column in SSMS by right clicking on the table and select Design to open a table in the design mode where you can select a column and set 'Identity Specification' property to 'Yes' along with increment and seed value, as shown below.

Now, let's insert a row into the Employee table.

INSERT INTO Employee VALUES ('Aparna' , 'Anand')

The above statement will insert a new record into the Employee table in all the columns, as shown below.

As you can see, we have not specified the value for the EmpID column. It automatically inserts a value for the identity column EmpID, which is 1. If you insert another record then it will be 2.

INSERT INTO Employee VALUES('Bill' , 'Gates');

The above statement will insert the following record.

Thus, it will automatically insert a value incremented by 1 from the last generated value of the identity column.

For any reason, if an insert fails or is rolled back, the EmpID number that was generated is lost and there will be gaps in the EmpID column.

Let's execute the following statement which will raise an error because it tries to enter NULL in the NOT NULL column.

INSERT INTO Employee VALUES('Ron' , NULL);

Now, execute the valid insert statement as shown below

INSERT INTO Employee VALUES('Ron', 'Kennedy');

The above statement will insert a row where the EmpID value will be 4 and not 3, as shown below. Thus, even if an error occurred in the transaction, the identity column value will still be increased.

Learn How to find gaps in identity column values and how to reset identity column value for more information.