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
Reset auto number (IDENTITY) column
Reset auto number (IDENTITY) column
Reset auto number (IDENTITY) column
Reset auto number (IDENTITY) column
Reset auto number (IDENTITY) column
Reset auto number (IDENTITY) column
Reset auto number (IDENTITY) column
Hope this post helps you.
Hi Jay,
Good to know, it helps you.
Thanks alot u really helped me alot…keep it up!!
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
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/