Equivalent to SQL Anywhere GET_IDENTITY?

[Jim C]

Is there an equivalent in mssql to SQL Anywhere's GET_IDENTITY which
reserves the next autoinc value for a table? Yes I know about
@@Identity and SCOPE_IDENTITY. I need to get the next autoinc value
_before_ I insert the record due to the way the existing application
works.

I've seen DBCC CHECKIDENT mentioned but that seems kludgy to me.

TIA,
Jim

[Jim C]

I don't believe in foreign keys.

Just kidding!

You are right, and yes that would be nice to have foreign keys and get
all the benefits of cascaded deletes and actual database enforced
integrity. Lots of heavy lifting to do to bring this app to that
point.

One can dream.

"We don't have time to stop for gas, we're already late."

Robert Klemme wrote:

On 28.11.2006 17:51, Jim C wrote:
Hm, I smell data inconsistency here. You certainly do not have foreign
keys on that id column, do you? Otherwise the DB would force you to
first insert the record that gets the id and then dependent records.

[Erland Sommarskog]

Jim C (ji**********@g mail.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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Robert Klemme]

On 28.11.2006 22:29, Jim C wrote:

!!!
Bad side effect: TRUNCATE TABLE resets the identity column to 1. Dumb,
poorly documented feature. Needs to have an optional NO IDENTITY RESET
clause.

Ooops, sorry for forgetting that.

So I have to use DELETE instead, that's ok.

Or you just leave the table alone and let it grow.

robert

[Jim C]

Erland Sommarskog wrote:

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.

So, you knowingly gave me an unusable answer, hmm. Ok. Thanks, I
guess. :-] And I disagree, it did not fit what I was asking for.
Using ident_current() in the way you suggest would be dangerous in a
multi-user application, which this application is.

No hard feelings? I'm very glad there's a community here willing to
answer my poorly explained, newbie-ish, corner-case questions!

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.

Thanks for that. I had a look at GUIDs, but as you say the cost of the
size is too high, since our application doesn't typically have enough
users at a site to warrant using them. Not to mention reworking a
couple hundred places in the code and the thought of support
complaining they can't find the children of a parent record easily
enough. :-)

Regards,
Jim