重置 SQL Server 数据库中列的标识(identity)值

Question

重置 SQL Server 数据库中列的标识(identity)值

3

我创建了一个应用程序并使用了从互联网上找到的以下代码来清除所有数据并重新设置表的标识列（Identity columns）的标识。

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'DELETE FROM ?' 
GO 

EXEC sp_MSForEachTable 'DBCC CHECKIDENT(''?'', RESEED, 0)'
GO

EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 
GO

但是我有两个没有Identity列的表格，这给了我一个问题，显示如下：

Msg 7997, Level 16, State 1, Line 616
'SupplierBalance'不包含标识列。

Msg 7997, Level 16, State 1, Line 616
'CustomerBalance'不包含标识列。

我想知道是否有一种方法可以重置只有标识列的表格的标识列，而不会出现错误。

谢谢。

- jayz

2

我相信使用 TRUNCATE 命令将重新设置你的 IDENTITY 列，因此你不必发出 RESEED 命令。 - Felix Pamittan

据我所知@felix是正确的。截断表不仅会重新设置标识列，而且也是删除所有表内容最快的方式，因为它没有在SQL日志中注册。 - Zohar Peled

@ZoharPeled，与普遍观点相反，TRUNCATE仅记录最小量的日志。http://dba.stackexchange.com/questions/30325/delete-vs-truncate/30347#30347 - Felix Pamittan

1

@felix，感谢你提供的好链接！ - Zohar Peled

“TRUNCATE TABLE？”在我的代码中无法使用。每当我使用它时，它会说表中仍然有外键约束。 - jayz

2个回答

0

1) 第一个参数表名

2) 第二个关键字（'RESEED'）用于重置标识

3) 第三个下一个标识值

  DBCC CHECKIDENT ('ResetIDClmn', RESEED, 5)

例子

if OBJECT_ID('ResetIDClmn') is not null
Begin
Drop table ResetIDClmn
 End
        Create table ResetIDClmn (P_Id int identity,abc varchar(10),PRIMARY KEY (P_Id))
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')

        Select * from ResetIDClmn

        --truncate table ResetIDClmn 

        delete ResetIDClmn where P_Id>5

        DBCC CHECKIDENT ('ResetIDClmn', RESEED, 5)
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Insert Into ResetIDClmn (abc) values ('sdfsfd')
        Select * from ResetIDClmn

- Ratha Krishnan

在我的表上运行DBCC CHECKIDENT（'[JBPM_TOKENVARIABLEMAP]'，RESEED，5）导致Msg 7997，Level 16，State 1，Line 1的错误消息。 'JBPM_TOKENVARIABLEMAP'不包含标识列。 - Thom

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Felix Pamittan · Accepted Answer

使用TRUNCATE命令可以替代使用DELETE命令并发出RESEED命令的操作。

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 
GO 

EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 
GO

使用TRUNCATE也比使用DELETE更有效率。Paul White在他的答案中解释道：

是的。出于以下几个原因，TRUNCATE TABLE更有效率：

需要的锁更少。截断通常只需要在表级别上获取一个架构修改锁（和每个取消分配的范围上的排它锁）。删除可能会以更低的（行或页）粒度获取锁，以及在任何取消分配的页面上获得独占锁。

只有截断可以保证从堆表中取消分配所有页面。即使为数据库启用了行版本隔离级别并指定了独占表锁提示，删除可能会在堆中留下空页面。

截断始终最小化记录日志（无论使用的恢复模型如何）。事务日志仅记录页面取消分配操作。

如果对象的大小大于等于128个范围，则截断可以使用延迟删除。延迟删除意味着实际的取消分配工作由后台服务器线程异步执行。

此外，我认为sp_MSforEachTable是未记录的。您可以在此处找到另一种替代方法。

由于部分表存在引用外键，您不能使用TRUNCATE而不删除约束。但仍可以使用DELETE。现在的问题在于RESEED，它会在没有IDENTITY列的表上产生错误。为解决这个问题，这里有一个动态SQL可供执行。它只会在表具有IDENTITY列时才进行RESEED：

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SET NOCOUNT ON;';

WITH Cte(tableName, hasIdentity) AS(
    SELECT t.name, CAST(ISNULL(ic.object_id, 0) AS BIT)
    FROM sys.tables t
    LEFT JOIN sys.identity_columns ic
        ON t.object_id = ic.object_id
    WHERE t.type = 'U'
)
SELECT @sql = @sql + CHAR(10) + 
    N'ALTER TABLE ' + QUOTENAME(tableName) + ' NOCHECK CONSTRAINT ALL;' + CHAR(10) +
    N'DELETE FROM ' + QUOTENAME(tableName) + ';' + CHAR(10) + 
    CASE
        WHEN hasIdentity = 1 THEN 
            N'DBCC CHECKIDENT(''' + QUOTENAME(tableName) + ''', RESEED, 0) WITH NO_INFOMSGS;' + CHAR(10)
        ELSE ''
    END +
    N'ALTER TABLE ' + QUOTENAME(tableName) + ' WITH CHECK CONSTRAINT ALL;'
FROM Cte

PRINT @sql;
EXEC sp_executesql @sql;