|
|
Posted by David Portas on 12/03/06 19:59
Erland Sommarskog wrote:
> 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.
>
Itzik's article is here:
http://www.sqlmag.com/Article/ArticleID/48165/sql_server_48165.html
It does overcome some of the disadvantages of an IDENTITY column and
I've used variations of it very successfully. I am looking forward to
the day when we get an engine-level implementation of sequences and I
never have to use an IDENTITY column again.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|