The forthcoming version of SQL Server, codenamed "Denali" will have a sequence type object that functions similar to Oracle's sequence. However, just like an identity, it can also result in gaps if a transaction rollback occurs during insert.

Even a custom solution involving a tally table can result in gaps, unless you serialize the entire transaction of retreiving the next ID, inserting the record(s), and then updating the next available ID. It has to be serialized (blocking), because allowing another process to grab the next ID in the interim, before the first process has successfully committed it's transaction, will inevitably result in a gap when rollbacks occur.

Really, I think that it would be best to re-factor whatever application functionality or business rule requires that the numbers be entirely consecutive with no gaps, and ideally this would involve going with a standard identity solution.

Another reason developers sometimes resort to using a custom ID sequencer is when an ID is shared across multiple tables, and each table needs a non-overlapping range of values. In this case you can still use an identity, but also use an appropriate identity seed and check constraint placed on each table.