Transact SQL :- Resetting Identity Value Using “DBCC CHECKIDENT”

Background

Adding pretty much static data into a table and opted to use an identity column as a primary key.

Noticed

Noticed an inconsequential difference between the documentation and my experience.

SQL

DDL

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

DML

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

Output

Here are the screenshots.

Output – Initial load

stock.item.data.reset.01.20191023.0214AM

Output – Consequent Load

stock.item.data.reset.02.20191023.0217AM

Explanation

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.

Documentation

DBCC CheckIdent

Image

Link

dbcc.checkident.01.20191023.0224AM

Commentary

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.

Source Code Control

GitHub

DBCC CheckIdent

Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s