Identity column in SQL Server - Part 7

If a column is marked as an identity column, then the values for this column are automatically generated, when you insert a new row into the table. The following, create table statement marks PersonId as an identity column with seed = 1 and Identity Increment = 1. Seed and Increment values are optional. If you don't specify the identity and seed they both default to 1.







Create Table tblPerson
(
PersonId int Identity(1,1) Primary Key,
Name nvarchar(20)
)

In the following 2 insert statements, we only supply values for Name column and not for PersonId column. 
Insert into tblPerson values ('Sam')
Insert into tblPerson values ('Sara')

If you select all the rows from tblPerson table, you will see that, 'Sam' and 'Sara' rows have got 1 and 2 as PersonId.

Now, if I try to execute the following query, I get an error stating - An explicit value for the identity column in table 'tblPerson' can only be specified when a column list is used and IDENTITY_INSERT is ON. 
Insert into tblPerson values (1,'Todd')

So if you mark a column as an Identity column, you dont have to explicitly supply a value for that column when you insert a new row. The value is automatically calculated and provided by SQL server. So, to insert a row into tblPerson table, just provide value for Name column.
Insert into tblPerson values ('Todd')

Delete the row, that you have just inserted and insert another row. You see that the value for PersonId is 2. Now if you insert another row, PersonId is 3. A record with PersonId = 1, does not exist, and I want to fill this gap. To do this, we should be able to explicitly supply the value for identity column. To explicitly supply a value for identity column
1. First turn on identity insert - SET Identity_Insert tblPerson ON
2. In the insert query specify the column list
    Insert into tblPerson(PersonId, Name) values(2, 'John')

As long as the Identity_Insert is turned on for a table, you need to explicitly provide the value for that column. If you don't provide the value, you get an error - Explicit value must be specified for identity column in table 'tblPerson1' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. 

After, you have the gaps in the identity column filled, and if you wish SQL server to calculate the value, turn off Identity_Insert.
SET Identity_Insert tblPerson OFF

If you have deleted all the rows in a table, and you want to reset the identity column value, use DBCC CHECKIDENT command. This command will reset PersonId identity column.
DBCC CHECKIDENT(tblPerson, RESEED, 0)

24 comments:

  1. if i have 3 rows with id 1 2 3 , i delete row with id 2 , i wanna it automaticlly to add id 2 on the next insert , is is possible ?
    (without SET Identity_Insert )

    ReplyDelete
    Replies
    1. Nope, to my knowledge, you have to use Identity_Insert, and have to specify the value you want to insert.

      Delete
  2. when try to xecute this query shows error

    DBCC CHECKINDENT("tblItem", RESEED,0)

    Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options

    ReplyDelete
    Replies
    1. Remove the double quotes for table name and try...

      Delete
    2. First Check Your Spelling . it is CHECKIDENT not CHECKINDENT and also remove double quotes from tablename

      Delete
    3. Instead of double quotes use single quotes

      DBCC CHECKINDENT('tblItem', RESEED,0)

      Delete
    4. Correct syntax is

      DBCC CHECKIDENT(tblItem, RESEED, 0)

      Changes mad are:-
      1. Its CHECKIDENT and not CHECKINDENT
      2. no single or double codes should be there while giving table name.

      Thanks

      Delete
  3. Hi
    taking the example you gave
    Create Table tblPerson
    (
    PersonId int Primary Key,
    Name nvarchar(20)
    )
    and then would like to add identity as well
    Alter table tblPerson
    add id int identity
    But fail, help please and sorry but Latin and not much English

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Hi Esteban ...

      First Created Table

      Create Table tblPerson
      (
      PersonId int Primary Key,
      Name nvarchar(20)
      )

      Now if u want to alter the PersonId column ,, first u need to drop the PersonId bcoz u cannot alter the Primary key column,,,since it is a primary key u cannot drop the PersonId directly..

      So first u need to drop the Index that is generated by default for the PersonId Primary key column ..

      To drop the Index .. expand the table name tblPerson.. then expand the Indexes folder..
      Now Right click on the Index Name and select delete option and click ok..

      So now the index that is associated with the Primary key column got deleted..

      Now to delete the primary key column .. use the below code...


      alter table tblPerson
      drop column PersonId

      Now u can see only the name column as shown below..

      select * from tblPerson

      Name
      ----
      (0 row(s) affected)

      Now add the column again with ur requirements (making identity column)..

      alter table tblPerson
      add PersonId int primary key identity

      Now u can see two columns..

      select * from tblPerson

      Name PersonId
      -------------------- -----------

      (0 row(s) affected)

      So now the PersonId is the Primary key identity column...

      Delete

    3. HI
      I'm trying to drop my index before drop the primary key column.
      but delete option hided on the menu.

      Delete
  4. USE test
    GO
    DBCC CHECKIDENT (tblPerson, RESEED, 5)
    GO

    ReplyDelete
  5. i have set identity(1,5) by mistake
    how do i alter it to identity(5,1) via sql command

    ReplyDelete
    Replies
    1. create the table..

      create table tblPers
      (
      Id int Identity(1,5),
      Name varchar(20)
      )


      Hi since Id here, is an identity column .. u cannot alter the Identity column directly,, so first u need to drop it and then add the Id column again as shown below...

      drop the column

      alter table tblPers
      drop column Id

      Add the column again

      alter table tblPers
      add Id int identity(5,1)

      I hope u got the answer..

      Delete
  6. You can't alter the existing columns for identity.
    You have 2 options,
    Create a new table with identity & drop the existing table
    Create a new column with identity & drop the existing column

    Alter Table Names Drop Column ID
    Go

    ReplyDelete
  7. Good Job Sir thank you very much.
    I have a question can i have identity column like A001 and increment by 1.

    ReplyDelete
    Replies
    1. To my Knowledge I think u can't because column identity is only applicable for integer valued comlumn

      Delete
  8. In an interview interviewer asked me if we do not declare identity column as primary key,then there is any problem

    ReplyDelete
    Replies
    1. No there will not be any problem, but you need to provide the value of primary key every time and make sure it is unique.

      Delete
  9. Even Truncate sets the identity to zero by deleting all records from table

    ReplyDelete
  10. DBCC CHECKIDENT('tblPerson', RESEED, 0)----correct one

    ReplyDelete
  11. Coming in 2021 to see how awesome your content is! Thanks much!

    ReplyDelete
  12. Thank you very much Mr. Venkat for your simple and clear explanations of these complicated topics. I have watched many videos on YouTube for SQL Server lessons, but believe that you are the best of all. You are very experienced and smart in teaching these topics. The other preference that you have is your great English communication.

    ReplyDelete

It would be great if you can help share these free resources