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]

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/Articl...ver_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/m...S,SQL.90).aspx
--

[Erland Sommarskog]

Jim C (ji**********@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, 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

[Jim C]

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]

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]

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]

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]

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]

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]

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