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.
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)]
- Seed is the first value of the identity column.
- 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.
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
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
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
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.