Reset identity seed after deleting records in SQL Server

Solution 1:

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.


Thanks to Solomon Rutzky the docs for the command are now fixed.

Solution 2:

DBCC CHECKIDENT ('TestTable', RESEED, 0)
GO

Where 0 is identity Start value

Solution 3:

Although most answers are suggesting RESEED to 0, many times we need to just reseed to next Id available

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)

This will check the table and reset to the next ID.