Guarantee SQL Server Identity Columns that two consecutive values differ by constant increments?

Posted on

Question :

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?

Answer :

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.

Leave a Reply

Your email address will not be published.