Posted by Erland Sommarskog on 11/28/06 22:29

Jim C (jim.cullison@gmail.com) writes:
> Nope, ident_current() won't work for me because it does not reserve the
> next autoinc value. It just peeks at what was inserted last. The help
> says it "Returns the last identity value generated for a specified
> table or view in any session and any scope." I can not see how it
> could reliably be used to reserve the next autoinc value in a
> multi-user system because by the time you read the value, increment it,
> and insert a new record another session could easily have read the same
> value and inserted a row with the value you're about to insert.

Hey, you asked a question, and I gave you the answer that best fitted
what you was asking for. I also pointed out that it was not thing you
could really use.

> The application does its own autoinc-like stuff now but fails with a
> modern sql server because it relies on the old database engine's very
> different locking methods. I think what I'll explore now is getting
> the id source column to be read and written inside a serializable
> transaction.

Rolling your own often works well, unless there is a high insertion
rate, in which case it will not scale too well:

BEGIN TRANSACTION

SELECT @nextid = coalesce(MAX(id), 0) + 1
FROM tbl WITH (HOLDLOCK, UPDLOCK)

INSERT tbl (id, ....
VALUES (@nextid, ....

COMMIT TRANSACTION

But then again, what you was asking for does have this implied: to know
what the next IDENTITY value will be, we need to lock it, so that on one
else grabs it.

There is a completely scalable alternative, though, if you are in need
of that: newid(). newid() returns a GUID, and you can retrieve as many
you want in advance. On SQL 2005 there is also newsequentialid() which
guarantees that your GUIDs are generated sequentially, which is good
to avoid fragementation in the primary key index.

But since a GUID is 16 bytes, it comes with a space cost.

--
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]