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

2021-12-01 00:00:00 azure-sql-database sql database sql-server-2008 sql-server

我已将记录插入到 SQL Server 数据库表中.该表定义了一个主键，并且自动递增标识种子设置为是".这样做主要是因为在 SQL Azure 中，每个表都必须定义一个主键和标识.

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.

但由于我必须从表中删除一些记录，这些表的身份种子将受到干扰，索引列(自动生成的增量为 1)将受到干扰.

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.

如何在删除记录后重新设置标识列，使该列按数字升序排列?

身份列不用作数据库中任何地方的外键.

The identity column is not used as a foreign key anywhere in database.

推荐答案

DBCC CHECKIDENT 管理命令用于重置身份计数器.命令语法为:

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 ]

示例:

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

以前版本的 Azure SQL 数据库不支持，但现在支持.

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

感谢所罗门Rutzky docs 现在已修复.

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

相关文章