我已经将记录插入到SQL Server数据库表中。该表定义了一个主键,并且自动递增标识种子被设置为“Yes”。这样做主要是因为在SQL Azure中,每个表都必须定义一个主键和标识。
但是由于我必须从表中删除一些记录,这些表的标识种子将受到干扰,索引列(自动生成的增量为1)也将受到干扰。
如何在删除记录后重置标识列,使该列具有升序数字顺序?
标识列在数据库中的任何地方都不能用作外键。
我已经将记录插入到SQL Server数据库表中。该表定义了一个主键,并且自动递增标识种子被设置为“Yes”。这样做主要是因为在SQL Azure中,每个表都必须定义一个主键和标识。
但是由于我必须从表中删除一些记录,这些表的标识种子将受到干扰,索引列(自动生成的增量为1)也将受到干扰。
如何在删除记录后重置标识列,使该列具有升序数字顺序?
标识列在数据库中的任何地方都不能用作外键。
当前回答
我刚刚成功地使用了DBCC CHECKIDENT
注意事项:
引用表名时不接受方括号 DBCC CHECKIDENT('TableName',RESEED,n)将重置回n+1 例如,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)
其他回答
对于一个完整的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
DBCC CHECKIDENT ('TestTable', RESEED, 0)
GO
其中0是标识起始值
我使用下面的脚本来做到这一点。只有一种情况下,它会产生一个“错误”,即如果你已经删除了表中的所有行,而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)
;
虽然大多数答案都建议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。
你可以CHECKIDENT重置种子
DBCC CHECKIDENT
(
table_name
[ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]
例子
DBCC CHECKIDENT ('TAble', reseed,0)
-- 示例查询
您可以用以下代码插入基本数据
向表中插入数据后,先创建一个表
步骤到步骤i显示数据和删除数据显示细节理解代码
结果代码:创建带有rest种子Id的表 我用的是车管局系统。Identity_columns表具有标识
--Create Table
DROP TABLE IF EXISTS ExampleTable
create table ExampleTable (Id Bigint identity(1,1), Name nvarchar(10))
--Insert to ExampleTable and Delete and Show identity
insert into ExampleTable (Name)
select 'Test1' as NAme union all select 'Test2' as NAme
select * from ExampleTable
| Id | Name |
| -------- | -----|
| 1 |Test1 |
| 2 |Test2 |
delete from ExampleTable
insert into ExampleTable (Name) select 'Test3' as NAme
select * from ExampleTable
| Id | Name |
| -------- | -----|
| 3 |Test3 |
delete from ExampleTable
首次检查数据 如果表中没有数据使用种子表
如果表中有数据,使用最大id
之后用CHECKIDENT改变种子
--Find seedTable
declare @reseed int=0
if(not exists( select top 1 * from ExampleTable))
begin
SELECT
@reseed=cast( seed_value as int)
FROM sys.tables tables
JOIN sys.identity_columns identity_columns
ON tables.object_id=identity_columns.object_id
where
tables.name='ExampleTable'
and OBJECT_SCHEMA_NAME(tables.object_id, db_id())='dbo'
set @reseed=@reseed -1
end
else
begin
--if Table Has Data and use Max id For seed
set @reseed=(select top 1 id from ExampleTable order by id desc)
end
DBCC CHECKIDENT ('ExampleTable', reseed,@reseed)
insert into ExampleTable
(Name)
select 'Test4' as NAme
select * from ExampleTable
| Id | Name |
| -------- | -----|
| 1 |Test4 |
GO