IDENTITY Property in SQL Server

 

IDENTITY  Property

Sometimes we need a column whose values can uniquely identifying the rows in the table. To achieve this purpose, this column should contains the unique values and it can’t contains 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 sometime 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 insert 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 )

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,

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 maintain the data in the table, sometimes we need to set this constraint off like when we importing 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

For Example,

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)

Summary:-
Identity column in the Sql server is a very useful property and it can be used to retrieve the data very quickly specially 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 its initial seed value.

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Basic Concepts, SQL Server and tagged , . Bookmark the permalink.

33 Responses to IDENTITY Property in SQL Server

  1. What does identity insert mean, and why would you want to turn it off when importing data?
    Is that a situation when the data being imported already has a StudentID and you want to maintain it?

    I ask because I've have been importing data from an Access Database into an SQL Server database, and the tables I'm importing into already have an Identity Column. When importing, I have simply been selecting the "Append data to exisiting table" option and 'ignoring' the PK (Autonumber) column in Access, bringing in only those columns that I need.

    The data comes into my table fine, but I've never had to select the "Enable Identity Insert" option to do so. What exactly does that option do?

    Thanks!

  2. Nice post very helpful

    dbakings

  3. Nice Post Sir!!!

    I think your last statement of this post needs to be corrected.

    i.e."In case of truncate command on a table the identity column value is reset to 0."

    As per my knowledge after Truncate, the counter for that column is reset with the seed value. Which you have passed during identity column creation.

  4. You pointed out two common mistakes that people usually make. It is really easy to forget to insert the data in the right column. The error may not even be noticed until much later and then you have to go through step by step to do the correction.

  5. Coder says:

    Even though i have been writing sql queries for couple of year i have always struggled updating existing datatypes and add new columns to existing tables. thanks for this tutorial.

  6. I find out some useful information for me here. So i want to say thank you for your sharing.

  7. Amazing post.Thanks for sharing among us.

  8. jack says:

    Knowledgeable article sir, Thanks a lot for sharing

  9. David says:

    Nice Post we can also check the Null value using this Command IfNULL() but when i used this command it is not working.
    Suggest me query how can I check the Null Value.

    • vivekjohari says:

      Hi David,
      Thanks for your valuable feedback. In SQL Server ISNull() command works not IFNULL(). IFNULL() is used in mysql database. Hope you got the answer of your query.

  10. LUDO says:

    Hi David,
    Even though i have been writing SQL queries for couple of year i have always struggled updating existing datatypes and add new columns to existing tables. thanks for this tutorial.

  11. Pingback: Mortgage Calculator

  12. Pingback: Nightmare Market Exit Scam

  13. Pingback: หวยยี่กี่

  14. Pingback: sen bir oğlansın

  15. Pingback: บริษัท ออล ครีเอตี้ แลนด์ จำกัด

  16. Pingback: www.idrpokerq.site

  17. Pingback: link vao 12bet

  18. Pingback: Samsara Market

  19. Pingback: Darknet Drogen

  20. Pingback: Samsara Market

  21. Pingback: mansion88

  22. Pingback: Empire Market

  23. Pingback: Drogen kaufen im Darknet

  24. Pingback: keo ca cuoc bong da truc tuyen

  25. Pingback: ca cuoc bong da ao truc tuyen

  26. Pingback: cdrqq

  27. Pingback: แทงบอลออนไลน์

  28. Pingback: Ignou Synopsis

  29. Pingback: granice

  30. Pingback: Klik hier

  31. Alisha Ross says:

    It’s actually a great and helpful piece of information.
    I’m satisfied that you shared this useful info with us.
    Please stay us informed like this. Thank you for sharing.

Leave a Reply