DBCC Command Not Working Inside Procedure

Question

I have below query. Logically, the procedure usp_mytran should RESEED the Identity to 1 for table dbo.Sales. But the last query is returning different values for Max_ID_Value and Current_Seed_Value. Can anyone please explain why DBCC command is not working inside procedure?

USE tempdb

--  Create table 
CREATE  TABLE dbo.Sales
(ID INT IDENTITY(1,1), Address VARCHAR(200))
GO

--  Procedure to Populate data into dbo.Sales
CREATE PROCEDURE usp_mytran 
AS
BEGIN
BEGIN TRANSACTION
    INSERT dbo.Sales
            ( Address )
    VALUES  ( 'Dwarka, Delhi' );

    --  Delete it for some reason
    DELETE FROM dbo.Sales;

    --  Code to check max ID value, and verify it again IDENTITY SEED
    DECLARE @MaxValue INT = (SELECT ISNULL(MAX(ID),1) FROM dbo.Sales)
    IF @MaxValue IS NOT NULL AND @MaxValue <> IDENT_CURRENT('dbo.Sales')
        DBCC CHECKIDENT ( 'tempdb.dbo.Sales', RESEED, @MaxValue );
ROLLBACK TRANSACTION
END

--  Ideally, this should RESEED the Identity of dbo.Sales table.
EXEC usp_mytran

--  Max_ID_Value & Current_Seed_Value should be same
SELECT ISNULL(MAX(ID),1) AS Max_ID_Value, IDENT_CURRENT('dbo.Sales') AS Current_Seed_Value FROM dbo.Sales

Please do execute procedure `usp_mytran` more than once to note the difference in values of *Max_ID_Value* and *Current_Seed_Value*. — Ajay Dwivedi, Oct 15 '16 at 12:31

score 3 · Answer 1 · answered Oct 15 '16 at 13:23

3

Sorry for answering my own question. As pointed by @Kannan Kandasamy, it is the ROLLBACK TRANSACTION code that is reverting back the work done by DBCC CHECKIDENT. So to make it work, I have created a job with name Reseed_Sales containing code to RESEED Identity for table dbo.Sales. Below is the final query for procedure usp_mytran.

--  Procedure to Populate data into dbo.Sales
ALTER PROCEDURE usp_mytran 
AS
BEGIN
BEGIN TRANSACTION
    INSERT dbo.Sales
            ( Address )
    VALUES  ( 'Dwarka, Delhi' );

    --  Delete it for some reason
    DELETE FROM dbo.Sales;

    --  Code to check max ID value, and verify it again IDENTITY SEED
    DECLARE @MaxValue INT = (SELECT ISNULL(MAX(ID),1) FROM dbo.Sales)
    IF @MaxValue IS NOT NULL AND @MaxValue <> IDENT_CURRENT('dbo.Sales')
        EXEC msdb..sp_start_job @job_name = 'Reseed_Sales'
ROLLBACK TRANSACTION
END

answered Oct 15 '16 at 13:23

Ajay Dwivedi

328
2
14

I don't know what you are trying to achieve but using identity doesn't look like the right solution. Your code has various issues under concurrency. – Martin Smith Oct 15 '16 at 15:49
Hi @MartinSmith, please check question [Identity key counter increment by one although it is in TRY Catch and Transaction is roll-backed ? SSMS 2008](http://stackoverflow.com/questions/40013274/identity-key-counter-increment-by-one-although-it-is-in-try-catch-and-transactio/40014054#40014054). Requirement was *if TRY block fails, then RESEED the Identity value in CATCH block, and rollback the transaction*. So this is what I am trying to achieve by keeping `ROLLBACK TRANSACTION` and within it tying to RESEED the identity using sql agent job all from within Procedure. – Ajay Dwivedi Oct 15 '16 at 16:12
1

If you want a column that doesn't have gaps `IDENTITY` is not the right tool for the job. You should realize this when you are having to do crazy things like spawn SQL agent jobs inside a simple CRUD procedure. Under concurrency you will certainly find your approach doesn't work anyway. – Martin Smith Oct 15 '16 at 19:13

score 1 · Accepted Answer · edited May 23 '17 at 12:06

Actually it is working as expected inside stored procedure: Rollback tran will rollback the checkident value - that is what happening in the code

--  Procedure to Populate data into dbo.Sales
alter PROCEDURE usp_mytran 
AS
BEGIN
    BEGIN TRANSACTION
    INSERT dbo.Sales
            ( Address )
    VALUES  ( 'Dwarka, Delhi' );

    --  Delete it for some reason
    DELETE FROM dbo.Sales;

    --  Code to check max ID value, and verify it again IDENTITY SEED
    DECLARE @MaxValue INT = (SELECT ISNULL(MAX(ID),1) FROM dbo.Sales)
    IF @MaxValue IS NOT NULL AND @MaxValue <> IDENT_CURRENT('test.dbo.Sales')
        DBCC CHECKIDENT ( 'test.dbo.Sales', RESEED, @MaxValue );
    ROLLBACK TRANSACTION
END

If you check ident_current now it shows 2 and on the next run it shows 4 etc because of ROLLBACK TRANSACTION

Now check after multiple executions "EXEC usp_mytran"

select IDENT_CURRENT('test.dbo.Sales')

You will see that checkident will not reset.

If we remove that transaction then CHECKIDENT will reseed to 1.

Commented transaction below

--  Procedure to Populate data into dbo.Sales
alter PROCEDURE usp_mytran 
AS
BEGIN
    --BEGIN TRANSACTION
    INSERT dbo.Sales
            ( Address )
    VALUES  ( 'Dwarka, Delhi' );

    --  Delete it for some reason
    DELETE FROM dbo.Sales;

    --  Code to check max ID value, and verify it again IDENTITY SEED
    DECLARE @MaxValue INT = (SELECT ISNULL(MAX(ID),1) FROM dbo.Sales)
    IF @MaxValue IS NOT NULL AND @MaxValue <> IDENT_CURRENT('test.dbo.Sales')
        DBCC CHECKIDENT ( 'test.dbo.Sales', RESEED, @MaxValue );
    --ROLLBACK TRANSACTION
END

Now check after multiple executions "EXEC usp_mytran"

select IDENT_CURRENT('test.dbo.Sales')

You will see the reseed value as '1'

Check for below example as well...

Can we rollback to original state after we have used DBCC CHECKIDENT to restart Identity column count?

DBCC Command Not Working Inside Procedure

2 Answers2