Adding pretty much static data into a table and opted to use an identity column as a primary key.
Noticed an inconsequential difference between the documentation and my experience.
use [tempdb] go set nocount on go set XACT_ABORT on go if schema_id('stock') is null begin exec('create schema [stock] authorization [dbo]') end go /* drop table [stock].[item]; */ if object_id('[stock].[item]') is null begin create table [stock].[item] ( [id] int not null identity(1,1) , [name] nvarchar(60) not null , [quantity] int not null constraint [constraint.default.stock.item.quanity] default 0 ) end go
use [tempdb] go set nocount on go set XACT_ABORT on go declare @transaction bit declare @bIdentityKeyReset bit declare @reseedValueAs0 int declare @reseedValueAs1 int set @transaction =0 set @transaction =1 set @reseedValueAs0 = 0 set @reseedValueAs1 = 1 set @bIdentityKeyReset = 1 if (@transaction =1) begin begin tran print 'Transaction Initiated' end if (@bIdentityKeyReset=1) begin DBCC CHECKIDENT ( '[stock].[item]' , RESEED , @reseedValueAs1 ) ; end insert into [stock].[item] ( [name] , [quantity] ) values ('Apple', 300) , ('Pear', 576) , ('Pencil', 950) , ('Sharpener', 1091) , ('Eraser', 1910) select * from [stock].[item] delete tblSI from [stock].[item] tblSI while (@@TRANCOUNT > 0) begin print 'Transaction Rolledback' rollback tran; end go
Here are the screenshots.
Output – Initial load
Output – Consequent Load
The first time we loaded data, we started off with an id of 1.
We employed DBCC CheckIdent hoping that consequent loads will reset our ID to 1.
But, No, it appears that the starting point is indeed 1, but incremented to 2 before assignment.
Though the documentation suggests that passing along a reseed value of 1 to “DBCC CHECKIDENT” is the way to go, our mileage is a bit different.
It appears that we will be better served with a reseed value of 0.