🤩 New Cool Developer Tools for you. Explore →
FREE JavaScript Video Series Start Learning →
FLAT 75% OFF All Interactive courses at flat ₹250 / $3.25 only. HURRRRRY!! Explore now
  Signup/Sign In
Tests
MCQs to test your knowledge.
Forum
Engage with the community.
Compilers
Compilers to execute code in browser.
← Back to Questions
Ask Question
Not satisfied by the Answer? Still looking for a better solution?
Ask Question

Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have a primary key and identity defined.

Now I have to delete some records from the database table, the identity seed for those tables will be disturbed and the index column will get disturbed.

How to reset the identity column after I deleted the records so that the column has sequence in ascending numerical order?

The identity column is not used as a foreign key anywhere in database.
database sql-server sql
by

4 Answers

kshitijrana14
The DBCC CHECKIDENT management command is used to reset identity counter. The command syntax is:
DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])

[ WITH NO_INFOMSGS ]

Example:
DBCC CHECKIDENT ('[TestTable]', RESEED, 0);

GO

It was not supported in previous versions of the Azure SQL Database but is supported now.
espadacoder11 


DBCC CHECKIDENT ('TestTable', RESEED, 0)

GO

Where 0 is identity Start value
RoliMishra
You can try this code. This will check the table and reset to the next ID.

declare @max int

select @max=max([Id]) from [TestTable]

if @max IS NULL   --check when max is returned as null

  SET @max = 0

DBCC CHECKIDENT ('[TestTable]', RESEED, @max)
sandhya6gczb
Use this code


DELETE FROM [TestTable]



DBCC CHECKIDENT ('[TestTable]', RESEED, 0)

GO

The first row will get the identity=1

Login / Signup to Answer the Question.

LoginSignup