6

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?

Improve this question
1
  • 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
    Commented Jul 19, 2011 at 17:42

1 Answer 1

Reset to default
14

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.

Improve this answer
5
  • 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
    Commented Jul 23, 2011 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
    Commented Jul 27, 2011 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
    Commented Nov 4, 2011 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
    Commented Nov 4, 2011 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
    Commented Nov 4, 2011 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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