Reset Identity Column Value in SQL Server (Identity Reset)
前言:今天在群里看到有人在问SQL Server自增值重置问题(sqlserver identiy column value reset )
闲话少说,直接上代码:
正文:
--create table
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--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
--insert test data into table
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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;
--delete data
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--delete data from test table delete from Test_1;
--reset identity
DBCC CHECKIDENT (Test_1, RESEED,0)
再次执行插入语句后查看结果
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--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;
结束语:DBCC CHECKIDENT (Test_1, RESEED,0) -- reset identity value
作者:Fly
出处:http://www.cnblogs.com/fly12300/
.NET技术交流群:70895254
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此声明,并在文章页面明显位置给出原文链接,否则作者将保留追究法律责任的权利。