SQL Server – Reset auto number (IDENTITY) column in SQL Server

How to reset auto number (IDENTITY) column in SQL Server?

Sometimes we need to reset the auto number – identity column in a table.

Using the DELETE statement is not enough. The statement only deletes the data but not reset the identity column.

We can reset the auto number (IDENTITY) column in SQL Server using DBCC CHECKIDENT command.

Following example shows how to reset auto number (IDENTITY) column the IDENTITY column

Reset auto number (IDENTITY) column

Hope this post helps you.

Varinder Sandhu

May 15, 2012 at 10:11 pm

Hi Jay,

Good to know, it helps you.
Bjimsons

May 26, 2012 at 6:12 pm

Thanks alot u really helped me alot…keep it up!!
Vijay Kumar

July 23, 2012 at 5:09 pm

Hi Varinder,

I have a requirement that the records may be deleted in between. As if like the Query — “delete from reset_identity where id in (3,4)”. Now, I want my next insert should either fill the position 3 (as it empty) or, the repositioning of the existing records will also be fine… How can get this approach?

Thanks in Advance.
-Vijay Kumar
- Varinder Sandhu
  
  July 25, 2012 at 8:06 pm
  
  Hi Vijay,
  
  You can insert the Identity Column values by enable IDENTITY_INSERT for the table.
  
  Please refer the given URL for this
  
  http://varindersandhu.in/2011/02/01/add-the-value-into-an-identity-column-in-sql-server/