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.