Reset identity seed after deleting records in SQL Server
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);GOIt 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.
DBCC CHECKIDENT ('TestTable', RESEED, 0)GOWhere 0 is identity Start value
Although most answers are suggesting RESEED to 0, many times we need to just reseed to next Id available
declare @max intselect @max=max([Id]) from [TestTable]if @max IS NULL --check when max is returned as null SET @max = 0DBCC CHECKIDENT ('[TestTable]', RESEED, @max)This will check the table and reset to the next ID.