Identity key counter increment by one although it

Question

Identity counter increment by one although it is in TRY Catch and Transaction is roll-backed ? SSMS 2008 is there any way i can stop it +1 or rollback it too.

Answer 1

In order to understand why this happened, Let's execute below sample code first-

USE tempdb

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

BEGIN TRANSACTION
    INSERT DBO.Sales
            ( Address )
    VALUES  ( 'Dwarka, Delhi' );

ROLLBACK TRANSACTION

Now, Execution plan for above query is-

The second last operator from right Compute Scalar is computing value for [Expr1003]=getidentity((629577281),(2),NULL) which is IDENTITY value for ID column. So this clearly indicates that IDENTITY values are fetched & Incremented prior to Insertion (INSERT Operator). So its by nature that even transaction rollback at later stage once created IDENTITY value is there.

Now, in order to reseed the IDENTITY value to Maximum Identity Value present in table + 1, you need sysadmin permission to execute below DBCC command -

DBCC CHECKIDENT   
 (   
    table_name  
        [, { NORESEED | { RESEED [, new_reseed_value ] } } ]  
)  
[ WITH NO_INFOMSGS ]

So the final query should include below piece of code prior to rollback statement:-

--  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 ( 'dbo.Sales', RESEED, @MaxValue ) 

--ROLLBACK TRANSACTION

So it is recommended to leave it on SQL Server.

Answer 2

You are right and the following code inserts record with [Col01] equal to 2:

CREATE TABLE [dbo].[DataSource]
(
    [Col01] SMALLINT IDENTITY(1,1)
   ,[Col02] TINYINT
);

GO

BEGIN TRY

    BEGIN TRANSACTION;

    INSERT INTO [dbo].[DataSource] ([Col02])
    VALUES (1);

    SELECT 1/0

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION
    END;

END CATCH;

GO

INSERT INTO [dbo].[DataSource] ([Col02])
VALUES (1);

SELECT *
FROM [dbo].[DataSource]

This is by design (as you can see in the documentation:

Consecutive values after server restart or other failures –SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

I try using NOCACHE sequence but it does not work on SQL Server 2012:

CREATE TABLE [dbo].[DataSource]
(
    [Col01] SMALLINT 
   ,[Col02] TINYINT
);

CREATE SEQUENCE [dbo].[MyIndentyty]
    START WITH 1  
    INCREMENT BY 1 
    NO CACHE;  
GO  


BEGIN TRY

    BEGIN TRANSACTION;

    INSERT INTO [dbo].[DataSource] ([Col01], [Col02])
    SELECT NEXT VALUE FOR [dbo].[MyIndentyty], 1

    SELECT 1/0

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION
    END;

END CATCH;

GO

INSERT INTO [dbo].[DataSource] ([Col01], [Col02])
SELECT NEXT VALUE FOR [dbo].[MyIndentyty], 1

SELECT *
FROM [dbo].[DataSource]

DROP TABLE [dbo].[DataSource];
DROP SEQUENCE [dbo].[MyIndentyty];

You can use MAX to solve this:

CREATE TABLE [dbo].[DataSource]
(
    [Col01] SMALLINT 
   ,[Col02] TINYINT
);


BEGIN TRY

    BEGIN TRANSACTION;

    DECLARE @Value SMALLINT = (SELECT MAX([Col01]) FROM [dbo].[DataSource]);

    INSERT INTO [dbo].[DataSource] ([Col01], [Col02])
    SELECT @Value, 1

    SELECT 1/0

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION
    END;

END CATCH;

GO

DECLARE @Value SMALLINT = ISNULL((SELECT MAX([Col01]) FROM [dbo].[DataSource]), 1);

INSERT INTO [dbo].[DataSource] ([Col01], [Col02])
SELECT @Value, 1

SELECT *
FROM [dbo].[DataSource]

DROP TABLE [dbo].[DataSource];

But you must pay attentions to your isolation level for potential issues:

If you want to insert many rows at the same time, do the following:

1. get the current max value
2. create table where to store the rows (that are going to be inserted) generating ranking (you can use identity column, you can use ranking function) and adding the max value to it
3. insert the rows