Click here to Skip to main content
15,860,943 members
Articles / Database Development / SQL Server

IDENTITY Property in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.17/5 (11 votes)
29 Aug 2010CPOL2 min read 61.6K   8   9
Identity property in SQL Server

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:

SQL
CREATE TABLE Student (Studentid int IDENTITY (1, 1) NOT NULL,
        Firstname nvarchar (200) NULL,Lastname nvarchar (200),Email nvarchar (100) NULL )

Here column 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:

SQL
insert into Student (Firstname,Lastname,Email)
              Values('Vivek', 'Johari', ‘vivekjohari@abc.com')

Here, we do not specify the value for the studentid column in the Insert statement.

Although 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:

SQL
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:

SQL
set identity_insert Student off

To set the Identity column property On, the following command is used:

SQL
Set Identity_insert Tablename On

For example:

SQL
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:

SQL
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:

SQL
dbcc checkident (Student, reseed, 10)

Summary

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.

Note

  1. Only one Identity column is possible for a table.
  2. In case of truncate command on a table, the identity column value is reset to 0.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Database Administrator
India India
I am currently working as a Senior DBA and have around 11 years of experience in database.

Degree:-
Master Degree in Computer(MCA)

Work experience:-
Designing of the database.
Database Optimization.
Writing Complex Stored Procedures,Functions,Triggers etc.
Designing and developing SSIS & DTS packages.
Designing SQL Reports using SSRS.
Database Server Maintenance.

Certification:-
Microsoft certified Sql DBA in Sql server 2008 (MCTS).
Microsoft certified BI professional in Sql server 2008 (MCTS).
Oracle certified profession DBA in ORACLE 10g (OCP)
certified profession DBA in ORACLE 9i (OCP)

My other publication
Technical Blog:- Technologies with Vivek Johari

Moderator and Blogger at BeyondRelational.com

Guest Author and Blogger at sqlservercentral.com

Comments and Discussions

 
GeneralMy vote of 2 Pin
qery13374-Nov-14 4:01
qery13374-Nov-14 4:01 
GeneralMy vote of 2 Pin
EngleA31-Aug-10 4:29
EngleA31-Aug-10 4:29 
GeneralMy vote of 1 Pin
bencr31-Aug-10 4:25
bencr31-Aug-10 4:25 
QuestionTabs and Property Pages ??? Pin
Middle Manager24-Aug-10 2:11
Middle Manager24-Aug-10 2:11 
GeneralMy vote of 2 Pin
Julien Bouvier23-Aug-10 13:33
Julien Bouvier23-Aug-10 13:33 
GeneralRe: My vote of 2 Pin
amit_vasu24-Aug-10 6:37
amit_vasu24-Aug-10 6:37 
In my view the article is good for basic understanding of the Identity property. The format and english used for any technical article should be simple so that anyone can understand it easily and therefore I think the english and format used in this article is good. Smile | :) Smile | :) Smile | :) Smile | :)


Thanks
Amit Kumar Singh
(Microsoft Technologies Expert)
GeneralRe: My vote of 2 Pin
EngleA31-Aug-10 4:30
EngleA31-Aug-10 4:30 
GeneralFix the formatting. Pin
R. Giskard Reventlov19-Aug-10 4:06
R. Giskard Reventlov19-Aug-10 4:06 
GeneralSuggestions Pin
Christian Graus17-Aug-10 10:42
protectorChristian Graus17-Aug-10 10:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.