Posted by Erland Sommarskog on 12/03/06 16:13

Tony Rogerson (tonyrogerson@sqlserverfaq.com) writes:
> Oh - you mean SQL Server 2007? Remember Mark Souza committed themselves
> to a 2 year release cycle....

Mark Souza? You are probably thinking of Paul Flessner.

> If I sat down and worked it out then what we have now in terms of CTE's,
> table structures, triggers I could do it - sadly, no time [at mo].

Itzik Ben-Gan has come up with an idea where you us an table with a single
identity column. You have a stored procedure that either starts a
transaction, or if a transaction is already in progress, it issues a
SAVE TRANSACTION. The procedure then inserts a row into the table and
retrieves the identity value with scope_identity(). Finally it rolls
back the transaction, either entirely or to the savepoint. Thus, table
is always empty, but it still produces a growing sequence. Since the table
is locked for only short duration, the concurrency is good.

A fairly convoluted solution, and likely to be less efficient than what
they have on Oracle.

Then again, in many cases a plain IDENTITY column will do.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]