With my DBA & developer lineage being in both Oracle PL/SQL as well as SQL Server T-SQL, I have been a big fan of Microsoft adding the Sequence object to SQL Server. You can find this new feature in the current public beta of SQL Server v-next, code named “Denali”. Download it here from Microsoft.com.
You can search through my MSSQLDUDE archives to find a few things that I’ve written about Sequences and how they can act as a good replacement for Identity columns. In those other postings, I touch on some of the things to thing about and re-architect since Sequence objects are detached from specific tables and do not have the Identity and Scope_Identity issues.
One thing that I tested which seems to still exist in Sequence objects, at least in CTP1 of Denali, is the “identity gap” that currently occurs in SQL Server Identity columns when you insert into a table with a column that has the identity property, but the insert fails. This condition is explain very well, very concisely and (what I like about it) in under 2 mins at SQL Share!
I tried the same technique with a Sequence object in Denali and got the same thing (screenshot below). The sequence object is incremented by 1 and then the insert fails because of a unique constraint on the table. But because the sequence was incremented first, before the actual insert, I have a gap in my sequence. This is not necessarily a big problem, just something to watch out for, as you most likely already are aware of with Identity columns.
This happens even if I moved the ID field in the table definition above to the end of the table row definition.