Equivalent to SQL Anywhere GET

Equivalent to SQL Anywhere GET_IDENTITY?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim C - 27 Nov 2006 23:29 GMT

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

Reply to this Message

David Portas - 28 Nov 2006 00:18 GMT

> 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
[quoted text clipped - 6 lines]
> TIA,
> Jim

You cannot reliably determine the next IDENTITY value, except maybe in
a single user system. There are other ways however:

http://www.sqlmag.com/Article/ArticleID/48165/sql_server_48165.html

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

Reply to this Message

Jim C - 28 Nov 2006 14:59 GMT

I don't subscribe to that site and can't see past the first page.

> You cannot reliably determine the next IDENTITY value, except maybe in
> a single user system. There are other ways however:
>
> http://www.sqlmag.com/Article/ArticleID/48165/sql_server_48165.html

Reply to this Message

Erland Sommarskog - 28 Nov 2006 08:06 GMT

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

The function ident_current() is the one you are looking for, but the value
it returns is global to all processes, so if you call ident_current(),
insert a row and then look at scope_identity() you may see a different
value, if another process was at it at the same time.

Signature

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

Reply to this Message

Jim C - 28 Nov 2006 15:13 GMT

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.

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.

> The function ident_current() is the one you are looking for, but the value
> it returns is global to all processes, so if you call ident_current(),
> insert a row and then look at scope_identity() you may see a different
> value, if another process was at it at the same time.

Reply to this Message

Erland Sommarskog - 28 Nov 2006 22:28 GMT

> 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
[quoted text clipped - 4 lines]
> 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.

Signature

Reply to this Message

Jim C - 29 Nov 2006 15:32 GMT

> 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
[quoted text clipped - 3 lines]
>
> 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

Reply to this Message

Robert Klemme - 28 Nov 2006 15:16 GMT

> 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
[quoted text clipped - 3 lines]
>
> I've seen DBCC CHECKIDENT mentioned but that seems kludgy to me.

You do not post where you need that information. If your insert is in a
stored procedure then SCOPE_IDENTITY after the fact should work.

If for some other reasons you really need the value beforehand, you can
emulate an Oracle sequence

-- test script
create table counter (
cnt int identity(1,1) primary key clustered
)

insert into counter default values
print 'ident: ' + cast(scope_identity() as varchar)
-- optional: truncate table counter

insert into counter default values
print 'ident: ' + cast(scope_identity() as varchar)
-- optional: truncate table counter

drop table counter

The you can pull identity values from that table and use them for the
insert.

Kind regards

robert

Reply to this Message

Jim C - 28 Nov 2006 16:51 GMT

Robert,

I'm accessing the tables with an application that uses ODBC for now
(it'll probably go some other more direct route in the near future as
things are refactored). The way it is architected it needs to know the
value before the insert occurs because of the how and when it passes
that value to child records in master-detail setups.

Your idea about emulating an Oracle sequence looks like it will be just
the ticket. Thanks!

Jim

> You do not post where you need that information. If your insert is in a
> stored procedure then SCOPE_IDENTITY after the fact should work.
>
> If for some other reasons you really need the value beforehand, you can
> emulate an Oracle sequence

Reply to this Message

Robert Klemme - 28 Nov 2006 17:17 GMT

> I'm accessing the tables with an application that uses ODBC for now
> (it'll probably go some other more direct route in the near future as
> things are refactored). The way it is architected it needs to know the
> value before the insert occurs because of the how and when it passes
> that value to child records in master-detail setups.

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.

> Your idea about emulating an Oracle sequence looks like it will be just
> the ticket. Thanks!

You're welcome!

robert

Reply to this Message

Jim C - 28 Nov 2006 22:09 GMT

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."

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

Reply to this Message

Jim C - 28 Nov 2006 21:29 GMT

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

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

Reply to this Message

Robert Klemme - 29 Nov 2006 08:50 GMT

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

Reply to this Message

Hugo Kornelis - 28 Nov 2006 20:24 GMT

(snip)

>If for some other reasons you really need the value beforehand, you can
>emulate an Oracle sequence
[quoted text clipped - 7 lines]
>print 'ident: ' + cast(scope_identity() as varchar)
>-- optional: truncate table counter

(snip)

Hi Robert,

I don't hink the optional TRUNCATE is a good idea, since TRUNCATE also
resets the identity seed :-)

If you don't want to fill up the table, here's a different suggestion
(blatantly stolen from Itzik Ben-Gan's excellent book):

BEGIN TRAN;
SAVE TRAN S1;
INSERT INTO counter DEFAULT VALUES;
SET @ident = SCOPE_IDENTITY();
ROLLBACK TRAN S1;
COMMIT TRAN;

The BEGIN TRAN and COMMIT TRAN are necessary for the SAVE TRAN and the
ROLLBACK with named savepoint to work. If you're already in a
transaction, the BEGIN TRAN will increase the tran counter and the
COMMIT TRAN will decrease it again. And the SAVE TRAN S1 / ROLLBACK TRAN
S1 combo ensures that the insert is undone (but the increment to the
identity seed and the variable assignment are left intact).

Signature

Hugo Kornelis, SQL Server MVP

Reply to this Message