首页 > 代码库 > Reset Identity Column Value in SQL Server (Identity Reset)

Reset Identity Column Value in SQL Server (Identity Reset)

2024-08-30 22:40:49   214人阅读

前言：今天在群里看到有人在问SQL Server自增值重置问题（sqlserver identiy column value reset ）

闲话少说，直接上代码：

正文：

--create table

技术分享 
--create test table 
if not exists(select * from sysobjects where name = ‘Test_1‘)
    begin 
        create table Test_1 (ID int identity(1,1) primary key,Name nvarchar(128),CreateDate datetime);
    end
else
    begin
        drop table Test_1;
        create table Test_1 (ID int identity(1,1) primary key,Name nvarchar(128),CreateDate datetime);
    end
go
View Code

--insert test data into table

技术分享 
declare @ID int;
select @ID = 1001;
while(@ID < 1101)
    begin
        insert into Test_1 values(‘Name‘+ CONVERT(nvarchar(128),@ID),GETDATE());
        select @ID = @ID + 1;
    end
--select data from test table
select * from Test_1;
View Code

技术分享

--delete data

技术分享 
--delete data from test table 
delete from Test_1;
View Code

--reset identity

DBCC CHECKIDENT (Test_1, RESEED,0)

再次执行插入语句后查看结果

技术分享 
--insert data into test table
declare @ID int;
select @ID = 1001;
while(@ID < 1101)
    begin
        insert into Test_1 values(‘NameS‘+ CONVERT(nvarchar(128),@ID),GETDATE());--PS:第二次Name值有改动
        select @ID = @ID + 1;
    end
select * from Test_1;
View Code

技术分享

结束语：DBCC CHECKIDENT (Test_1, RESEED,0) -- reset identity value

Reset Identity Column Value in SQL Server (Identity Reset)


联系
我们