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

[David Portas]

Re: Equivalent to SQL Anywhere GET_IDENTITY?

Jim C wrote:

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

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

--

[Erland Sommarskog]

Re: Equivalent to SQL Anywhere GET_IDENTITY?

Jim C (jim.cullison@g mail.com) writes:

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.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[Jim C]

Re: Equivalent to SQL Anywhere GET_IDENTITY?

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

David Portas wrote:

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/Articl...ver_48165.html

[Jim C]

Re: Equivalent to SQL Anywhere GET_IDENTITY?

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.

Erland Sommarskog wrote:

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.

[Robert Klemme]

Re: Equivalent to SQL Anywhere GET_IDENTITY?

On 28.11.2006 00:29, Jim C wrote:

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.

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_iden tity() as varchar)
-- optional: truncate table counter

insert into counter default values
print 'ident: ' + cast(scope_iden tity() 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

[Jim C]

Re: Equivalent to SQL Anywhere GET_IDENTITY?

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


Robert Klemme wrote:

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

[Robert Klemme]

Re: Equivalent to SQL Anywhere GET_IDENTITY?

On 28.11.2006 17:51, Jim C wrote:

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

[Hugo Kornelis]

Re: Equivalent to SQL Anywhere GET_IDENTITY?

On Tue, 28 Nov 2006 16:16:00 +0100, Robert Klemme wrote:

(snip)

>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_iden tity() 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).

--
Hugo Kornelis, SQL Server MVP

[Jim C]

Re: Equivalent to SQL Anywhere GET_IDENTITY?

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

[Jim C]

Re: Equivalent to SQL Anywhere GET_IDENTITY?

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]

Re: Equivalent to SQL Anywhere GET_IDENTITY?

Jim C (jim.cullison@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, esquel@sommarsk og.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

[Robert Klemme]

Re: Equivalent to SQL Anywhere GET_IDENTITY?

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]

Re: Equivalent to SQL Anywhere GET_IDENTITY?


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