Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Now that's an approximate sequence of operations Im performing:

SET IDENTITY_INSERT <table-name> ON;
INSERT SOMETHING to <table-name> with explicitly specifyed id
DECLARE @oldID bigint
SELECT @oldID = <some simple logic here>
DBCC CHECKIDENT ('<table-name>', RESEED, @oldID) WITH NO_INFOMSGS;
SET IDENTITY_INSERT <table-name> OFF;

So, as you see, everything's simple: basically, I insert record with explicit ID, then I want to return the identity seed back to its previous value (because the insert will obviously shift it).

I need this kind of strange logic (from the first POW) becase I can have ID-s in a large range (billions of rows) of values where the first digit has some special meaning.

For instance, I have current identity seed 1000007777. Then I want to insert a record with ID 2000007777 (this will shift identity to 2000007778). But I want to turn it back to 1000007777, because I believe that I will never reach the point when an identity collision will occur (I just don't operate such volumes of data). As I said, the first digit serves special purposes.

Now, the problem is, if I rollback the transaction after insert, the identity will not get rolled back (as i got it, that's the design of SQL server, and I believe many DB providers).

I can handle this manually if I am the one who conciously performes the rollback. But what if there is deadlock (or some error) right at the point before reseed?

How can I handle that (except handling the exception and performing unconditional reseed)? Any ideas?

share|improve this question
1  
First you need to ask yourself: why do you need to prevent gaps in the identity? What value does "no gaps" provide? Why don't you implement your own solution rather than use IDENTITY if this goal is that important to you? –  Aaron Bertrand May 15 '13 at 17:42
2  
If the first digit is significant, you really should have it as a separate field! –  JNK May 15 '13 at 17:58
2  
@JNK or a column, even. :-) –  Aaron Bertrand May 15 '13 at 18:51
    
I am not the original developer. There are reasons for this, and separating values is not an option. Finally, I'm not asking for design improvements. No offence, just clarifying. –  Artur Udod May 16 '13 at 9:04

2 Answers 2

up vote 1 down vote accepted

I believe your best bet is going to be to use a TRY-CATCH. I've done two things here. First declare and get your "oldID" before your transaction begins. This will help to keep your transaction time down and avoid deadlocks as much as possible. Second I've put the "core" of the rest of it into a TRY block and then in the CATCH block put the reseed again. This way regardless of the error you will re-set the seed. I believe this will work even in a deadlock situation.

DECLARE @oldID bigint
SELECT @oldID = MAX(id) from identtest where id<3000

BEGIN TRANSACTION

BEGIN TRY
    SET IDENTITY_INSERT identtest ON;
    INSERT INTO identtest (id, x) VALUES (4000,'y');
    DBCC CHECKIDENT ('identtest', RESEED, @oldID) WITH NO_INFOMSGS;
    SET IDENTITY_INSERT identtest OFF;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    DBCC CHECKIDENT ('identtest', RESEED, @oldID) WITH NO_INFOMSGS;
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

DBCC CHECKIDENT ('identtest', NORESEED)

All of that being said you may be better off switching your logic around a bit. Would it be possible for you to set your "normal" inserts to start at say 20000000 and then your IDENTITY_INSERTS to start at 10000000? This way regardless of your insert the seed will stay at the larger "normal" value. It sounds like you are a bit far in to do this, but you never know.

share|improve this answer
    
It seems that DBCC CHECKIDENT takes a numeric literal as third argument, not a variable –  Andomar May 15 '13 at 17:31
    
Actually it works just fine with a variable. –  Kenneth Fisher May 15 '13 at 17:36
    
You're right! I wonder if that's new in 2012 or if I missed it before. –  Andomar May 15 '13 at 17:42
    
Actually I tested it using SQL 2008. I can go further back if you like. –  Kenneth Fisher May 15 '13 at 18:43
    
Edited my answer to include a transaction. I had forgotten it earlier. –  Kenneth Fisher May 15 '13 at 18:44

You could use a try ... catch block:

-- Create test table
if object_id('YourTable') is not null
    drop table YourTable
create table YourTable (id int identity);

-- Safeguarded insert
set identity_insert YourTable on;
begin try
    insert YourTable (id) values (100);

    -- this generates an exception    
    declare @oops int = cast('a' as int)
end try
begin catch
    select  text
    from    sys.messages
    where   message_id = error_number()
            and language_id = 1033
end catch

-- Reset identity seed
declare @oldid int;

-- Prevent other processes from modifying the `max`
-- between our select and reseed
set transaction isolation level serializable
begin transaction
select  @oldid = isnull(max(id),0)
from    YourTable with (holdlock)
where   id < 100;

dbcc checkident ('YourTable', reseed, @oldid);
commit transaction
set transaction isolation level read committed
set identity_insert YourTable off;

-- Display table and identity value    
select  *
from    dbo.YourTable;

dbcc checkident(YourTable, noreseed);

This prints:

text
-----------------------------------------------------------------------------
Conversion failed when converting the %ls value '%.*ls' to data type %ls.

Checking identity information: current identity value '100'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
id
-----------
100

Checking identity information: current identity value '1', current column value '100'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
share|improve this answer
    
I don't know how safe this approach will be at the default isolation level. What if two users step through this at the same time? –  Aaron Bertrand May 15 '13 at 17:43
    
@AaronBertrand: Good point, the question hints at deadlocks, which imply concurrency. I think you can avoid the worst by protecting the max() and reseed with a serializable transaction. –  Andomar May 15 '13 at 17:52
    
Thank you, although I was asking for a solution without exception handling for these 5 lines. But it seems to me there's no other adequate solution. So thank you again. –  Artur Udod May 16 '13 at 9:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.