我已将记录插入到 SQL Server 数据库表中。该表定义了一个主键,并且自动增量标识种子设置为“是”。这样做主要是因为在 SQL Azure 中,每个表都必须定义一个主键和标识。
但由于我必须从表中删除一些记录,这些表的标识种子将受到干扰,并且索引列(自动生成,增量为 1)将受到干扰。
删除记录后如何重置标识列,以便该列按数字升序排列?
标识列不用作数据库中任何地方的外键。
DBCC CHECKIDENT
管理命令用于重置身份计数器。命令语法为:
DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])
[ WITH NO_INFOMSGS ]
例子:
DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO
它在以前版本的 Azure SQL 数据库中不受支持,但现在受支持。
多亏了 Solomon Rutzky,命令的 docs 现在已修复。
DBCC CHECKIDENT ('TestTable', RESEED, 0)
GO
其中 0 是 identity
起始值
DELETE
而不是 TRUNCATE
而为空的情况,在这种情况下它也是 new_reseed+value + 1
。我写了一篇关于这个的帖子,通过一些测试展示了实际行为,并更新了实际的文档(现在我们可以,因为它在 GitHub 上):How Does DBCC CHECKIDENT Really Work When Resetting the Identity Seed (RESEED)?。
尽管大多数答案都建议将 RESEED 设置为 0,但很多时候我们需要重新设置为下一个可用的 ID
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)
这将检查表并重置为下一个 ID。
declare @max int select @max=ISNULL(max([Id]),0) from [TestTable]; DBCC CHECKIDENT ('[TestTable]', RESEED, @max );
应该注意的是,如果通过 DELETE
(即没有 WHERE
子句)从表中删除 所有 数据,那么只要 a) 权限允许,并且 b ) 没有引用该表的 FK(此处似乎就是这种情况),使用 TRUNCATE TABLE
将是首选,因为它执行更有效的 DELETE
并且 重置 IDENTITY
种子同时。以下详细信息取自 TRUNCATE TABLE 的 MSDN 页面:
与 DELETE 语句相比,TRUNCATE TABLE 具有以下优点: 使用较少的事务日志空间。 DELETE 语句一次删除一个行,并在事务日志中为每个删除的行记录一个条目。 TRUNCATE TABLE 通过解除分配用于存储表数据的数据页来移除数据,并且仅在事务日志中记录页解除分配。通常使用较少的锁。当使用行锁执行 DELETE 语句时,表中的每一行都被锁定以进行删除。 TRUNCATE TABLE 始终锁定表(包括模式 (SCH-M) 锁)和页,但不锁定每一行。无一例外,零页留在表中。执行 DELETE 语句后,表仍然可以包含空页。例如,如果没有排他 (LCK_M_X) 表锁,就无法释放堆中的空页。如果删除操作不使用表锁,则表(堆)将包含许多空页。对于索引,删除操作可能会留下空页面,尽管这些页面将通过后台清理进程快速释放。如果表包含标识列,则该列的计数器将重置为为该列定义的种子值。如果没有定义种子,则使用默认值 1。要保留标识计数器,请改用 DELETE。
所以以下内容:
DELETE FROM [MyTable];
DBCC CHECKIDENT ('[MyTable]', RESEED, 0);
变成了:
TRUNCATE TABLE [MyTable];
有关限制等的更多信息,请参阅 TRUNCATE TABLE
文档(上面链接)。
我尝试了 @anil shahs
答案,它重置了身份。但是当插入一个新行时,它得到了 identity = 2
。因此,我将语法更改为:
DELETE FROM [TestTable]
DBCC CHECKIDENT ('[TestTable]', RESEED, 0)
GO
然后第一行将获得标识 = 1。
尽管大多数答案都建议 RESEED
到 0
,虽然有些人认为这是 TRUNCATED
表的一个缺陷,但 Microsoft 有一个解决方案排除了 ID
DBCC CHECKIDENT ('[TestTable]', RESEED)
这将检查表并重置为下一个 ID
。从 MS SQL 2005 到现在,这一直可用。
https://msdn.microsoft.com/en-us/library/ms176057.aspx
Checking identity information: current identity value '[incorrect seed]', current column value '[correct seed]'.
,但在新插入时它仍在使用不正确的种子。
发出 2 命令可以解决问题
DBCC CHECKIDENT ('[TestTable]', RESEED,0)
DBCC CHECKIDENT ('[TestTable]', RESEED)
第一个将标识重置为零,然后将其设置为下一个可用值--jacob
我刚刚成功使用了DBCC CHECKIDENT
注意事项:
不接受引用表名方括号时
DBCC CHECKIDENT('TableName',RESEED,n) 将重置回 n+1 例如 DBCC CHECKIDENT('tablename',RESEED,27) 将从 28 开始
例如 DBCC CHECKIDENT('tablename',RESEED,27) 将从 28 开始
如果您在未设置新的起始 ID 时遇到问题 - 请注意这一点,您可以通过以下方式解决此问题:
DECLARE @NewId as INT
SET @NewId = (SELECT MAX('TableName')-1 AS ID FROM TableName)
DBCC CHECKIDENT('TableName',RESEED,@MaxId)
@雅各布
DBCC CHECKIDENT ('[TestTable]', RESEED,0)
DBCC CHECKIDENT ('[TestTable]', RESEED)
为我工作,我只需要先从表中清除所有条目,然后在删除后将上述内容添加到触发点中。现在,每当我删除一个条目时,都会从那里获取。
首选 Truncate
表,因为它会清除记录、重置计数器并回收磁盘空间。
Delete
和 CheckIdent
应仅在外键阻止您截断时使用。
使用新 ID 重置标识列...
DECLARE @MAX INT
SELECT @MAX=ISNULL(MAX(Id),0) FROM [TestTable]
DBCC CHECKIDENT ('[TestTable]', RESEED,@MAX)
我使用以下脚本来执行此操作。只有一种情况会产生“错误”,即如果您已从表中删除所有行,并且 IDENT_CURRENT
当前设置为 1,即表中只有一行开始。
DECLARE @maxID int = (SELECT MAX(ID) FROM dbo.Tbl)
;
IF @maxID IS NULL
IF (SELECT IDENT_CURRENT('dbo.Tbl')) > 1
DBCC CHECKIDENT ('dbo.Tbl', RESEED, 0)
ELSE
DBCC CHECKIDENT ('dbo.Tbl', RESEED, 1)
;
ELSE
DBCC CHECKIDENT ('dbo.Tbl', RESEED, @maxID)
;
这是一个常见的问题,答案总是一样的:不要这样做。身份值应被视为任意值,因此没有“正确”顺序。
运行此脚本以重置标识列。您将需要进行两项更改。将 tableXYZ 替换为您需要更新的任何表。此外,标识列的名称需要从临时表中删除。这是在一个有 35,000 行和 3 列的表上瞬间完成的。显然,备份表并首先在测试环境中尝试。
select *
into #temp
From tableXYZ
set identity_insert tableXYZ ON
truncate table tableXYZ
alter table #temp drop column (nameOfIdentityColumn)
set identity_insert tableXYZ OFF
insert into tableXYZ
select * from #temp
在开发过程中,我一直在尝试为大量表完成此操作,这很有魅力。
DBCC CHECKIDENT('www.newsType', RESEED, 1);
DBCC CHECKIDENT('www.newsType', RESEED);
因此,您首先将其强制设置为 1,然后将其设置为表中存在的行的最高索引。快速轻松地休息 idex。
DBCC CHECKIDENT (<TableName>, reseed, 0)
这会将当前标识值设置为 0。
在插入下一个值时,标识值递增到 1。
使用这个存储过程:
IF (object_id('[dbo].[pResetIdentityField]') IS NULL)
BEGIN
EXEC('CREATE PROCEDURE [dbo].[pResetIdentityField] AS SELECT 1 FROM DUMMY');
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pResetIdentityField]
@pSchemaName NVARCHAR(1000)
, @pTableName NVARCHAR(1000) AS
DECLARE @max INT;
DECLARE @fullTableName NVARCHAR(2000) = @pSchemaName + '.' + @pTableName;
DECLARE @identityColumn NVARCHAR(1000);
SELECT @identityColumn = c.[name]
FROM sys.tables t
INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.columns c ON c.[object_id] = t.[object_id]
WHERE c.is_identity = 1
AND t.name = @pTableName
AND s.[name] = @pSchemaName
IF @identityColumn IS NULL
BEGIN
RAISERROR(
'One of the following is true: 1. the table you specified doesn''t have an identity field, 2. you specified an invalid schema, 3. you specified an invalid table'
, 16
, 1);
RETURN;
END;
DECLARE @sqlString NVARCHAR(MAX) = N'SELECT @maxOut = max(' + @identityColumn + ') FROM ' + @fullTableName;
EXECUTE sp_executesql @stmt = @sqlString, @params = N'@maxOut int OUTPUT', @maxOut = @max OUTPUT
IF @max IS NULL
SET @max = 0
print(@max)
DBCC CHECKIDENT (@fullTableName, RESEED, @max)
go
--exec pResetIdentityField 'dbo', 'Table'
只是重新审视我的答案。我在 sql server 2008 r2 中遇到了您应该注意的奇怪行为。
drop table test01
create table test01 (Id int identity(1,1), descr nvarchar(10))
execute pResetIdentityField 'dbo', 'test01'
insert into test01 (descr) values('Item 1')
select * from test01
delete from test01
execute pResetIdentityField 'dbo', 'test01'
insert into test01 (descr) values('Item 1')
select * from test01
第一个选择产生 0, Item 1
。
第二个产生 1, Item 1
。如果您在创建表后立即执行重置,则下一个值为 0。老实说,我并不奇怪微软无法正确处理这些内容。我发现它是因为我有一个脚本文件来填充引用表,我有时会在重新创建表后运行,有时会在表已创建时运行。
对于完整的 DELETE 行并重置 IDENTITY 计数,我使用它(SQL Server 2008 R2)
USE mydb
-- ##################################################################################################################
-- DANGEROUS!!!! USE WITH CARE
-- ##################################################################################################################
DECLARE
db_cursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_CATALOG = 'mydb'
DECLARE @tblname VARCHAR(50)
SET @tblname = ''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tblname
WHILE @@FETCH_STATUS = 0
BEGIN
IF CHARINDEX('mycommonwordforalltablesIwanttodothisto', @tblname) > 0
BEGIN
EXEC('DELETE FROM ' + @tblname)
DBCC CHECKIDENT (@tblname, RESEED, 0)
END
FETCH NEXT FROM db_cursor INTO @tblname
END
CLOSE db_cursor
DEALLOCATE db_cursor
GO
除非您将整个表清理干净,否则重新设置为 0 不是很实用。
否则,安东尼雷蒙德给出的答案是完美的。首先获取标识列的最大值,然后使用最大值对其进行播种。
尽可能使用 TRUNCATE 而不是删除所有记录总是更好,因为它也不使用日志空间。
如果我们需要删除并需要重置种子,请始终记住,如果从未填充过表并且您使用了 DBCC CHECKIDENT('tablenem',RESEED,0)
,那么第一条记录将获得身份 = 0,如 msdn documentation 中所述
在您的情况下,只需重建索引,不要担心丢失一系列身份,因为这是一种常见情况。
TRUNCATE
会阻止 ROLLBACK
按预期行事? ROLLBACK 仍然回滚。即使 DB 设置为 BULK_LOGGED
。
第一:身份规范只是:“否”>>保存数据库执行项目
之后:身份规范只是:“是”>>保存数据库执行项目
你的数据库ID,PK从1开始>>
不定期副业成功案例分享
DBCC CHECKIDENT
:azure.microsoft.com/en-us/documentation/articles/… 不过,对于这种特殊情况,我仍然推荐 TRUNCATE TABLE :)