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.

I posed a question about Denali sequences here and another question about emulation of identity columns in Oracle Global Tables here.

I always thought that I could trust that identity values increment by constant increments. On the other side, I know that using sequences I can never be sure that there are not some gaps in them.

Is it time to adapt to gaps in sequences or will it be better to stay with identity columns in temporary tables?

share|improve this question
5  
You need to adjust to gaps, and never believe that auto-generated values have any meaning whatsoever. Use ROW_NUMBER() /* x your constant increment */ OVER (ORDER BY Sequence) if you want a proper "gapless sequence" at runtime, but even this can be affected by DELETE operations between runs. –  Aaron Bertrand Jul 19 '11 at 17:42
add comment

1 Answer 1

up vote 13 down vote accepted

You can't guarantee contiguous or consecutive identity values in SQL Server

Also:

  • Loading a temp table doesn't guarantee consecutive values. Saying that, this is harder to prove, and works in practice (as many folk rely on it), but be warned...

You have to adapt to gaps in your numbering, however generated.

share|improve this answer
    
Think twice. Your link shows that a single session doesn't get gapeless values. I can't say that is unexpected. Looking at Foo I saw no gaps in the ids of that table: select max(id) - MIN(id) + 1, COUNT(*) from foo –  bernd_k Jul 23 '11 at 17:07
    
@bernd_k: the link proves that inserting a 1000 rows in one go (any insert etc is atomic) will not give contiguous values for that 1000 rows. I ran the code and I got gaps within a few seconds. Separate inserts are of course not contiguous: but that isn't the point of the link. And you asked "Guarantee SQL Server Identity Columns that two consecutive values differ by constant increments?". The link proves "no" for any single INSERT –  gbn Jul 27 '11 at 6:23
    
Well, if you know you are the only one writing to the table then yes, you can trust that the generated IDENTITY values will be contiguous for an atomic insert. Isn't that much true? –  Nick Chammas Nov 4 '11 at 18:19
    
@Nick: At to a seminar I saw, with Itzik Ben-Gan as the guest, he stated that it isn't guaranteed for temp tables. And MS know this, which is why you won't see it on any MS site... –  gbn Nov 4 '11 at 18:27
    
What about for a regular table? The root of the issue is that the function responsible for generating identity values is not transactionally bound, which is what Martin's test exploits. Can't think of why you would get gaps if nothing else was touching the table, temp or regular. –  Nick Chammas Nov 4 '11 at 18:53
add comment

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.