Sometimes, we need a column whose values can uniquely identifying the rows in the table. To achieve this purpose, this column should contain the unique values and it can’t contain the
NULL or empty values. We can use this column in the “
Where” clause of the
Select statement to retrieve the data from the table quickly.
But as a human being, we sometimes forget to insert the data into this column or sometimes, we insert the duplicate data into this column. So it will be very helpful if the SQL Server itself inserts the value in this column whenever we try to insert any row into the table. SQL Server does this with the help of the identity column. We can set the initial value for this column and the value which the previous value is incremented to get the new value. We sometimes use this like the primary key of the table.
For example, suppose we want to create a table named
student whose structure is given below:
CREATE TABLE Student (Studentid int IDENTITY (1, 1) NOT NULL,
Firstname nvarchar (200) NULL,Lastname nvarchar (200),Email nvarchar (100) NULL )
Studentid is defined as the identity column. In the
Identity column, the value of the first argument defined the initial value for this column and the value of the second argument defined the value used to add in the last value of this column for getting the next value.
Now if we want to insert the row in the table
Student, we do not need to specify the value for the column
Studentid. For example:
insert into Student (Firstname,Lastname,Email)
Values('Vivek', 'Johari', ‘firstname.lastname@example.org')
Here, we do not specify the value for the
studentid column in the
Identity column is very useful in maintaining the data in the table, sometimes, we need to set this constraint off like when we import the data into the database.
To set the
Identity column property off, the following command is used:
Set Identity_insert Tablename Off
For example, if we want to keep the identity column (
studentid) property off for the
Student table, we need to use the following SQL Query:
set identity_insert Student off
To set the
Identity column property
On, the following command is used:
Set Identity_insert Tablename On
set identity_insert Student on
We can also reset the values of the identity column to some different value with the help of the following command:
dbcc checkident (Tablename, reseed, 10)
For example, to reset the value of the
studentid column of the table
Student, we need to use the following SQL Command:
dbcc checkident (Student, reseed, 10)
Identity column in the SQL server is a very useful property and it can be used to retrieve the data very quickly especially in the table where no primary key is defined.
- Only one
Identity column is possible for a table.
- In case of truncate command on a table, the identity column value is reset to