在 SQL Server 中删除记录后重置标识种子

Reset identity seed after deleting records in SQL Server(在 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.

But since I have to delete some records from the table, the identity seed for those tables will be disturbed and the index column (which is auto-generated with an increment of 1) will get disturbed.

How can I 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.

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.

这篇关于在 SQL Server 中删除记录后重置标识种子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！