Get next unique ID from a table before insert @@identity / Sequence

[brent.ryan@gmail.com]

How do I get the next int value for a column before I do an insert in
MY SQL Server 2000? I'm currently using Oracle sequence and doing
something like:

select seq.nextval from dual;

Then I do my insert into 3 different table all using the same uniqueID.

I can't use the @@identity function because my application uses a
connection pool and it's not garanteed that a connection won't be used
by another request so under a lot of load there could be major problems
and this doens't work:

insert into <table>;
select @@identity;

This doesn't work because the select @@identity might give me the value
of an insert from someone else's request.

Thanks,

Brent

[Hugo Kornelis]

Re: Get next unique ID from a table before insert @@identity / Sequence

On 16 Mar 2005 14:58:25 -0800, brent.ryan@gmai l.com wrote:
[color=blue]
>How do I get the next int value for a column before I do an insert in
>MY SQL Server 2000? I'm currently using Oracle sequence and doing
>something like:
>
>select seq.nextval from dual;
>
>Then I do my insert into 3 different table all using the same uniqueID.
>
>I can't use the @@identity function because my application uses a
>connection pool and it's not garanteed that a connection won't be used
>by another request so under a lot of load there could be major problems
>and this doens't work:
>
>insert into <table>;
>select @@identity;
>
>This doesn't work because the select @@identity might give me the value
>of an insert from someone else's request.
>
>Thanks,
>
>Brent[/color]

Hi Brent,

Create a stored procedure that starts a transaction, inserts into the
first table, retrieves the identity value used (with SCOPE_IDENTITY, the
recommended method in SQL Server 2000), uses it to insert data into the
other two table, then commits the transaction (or rolls it back if
anything went wrong).

Calling the server three times for three inserts is not only incurring
the overhead of more roundtrips then necessary, you also run the risk of
getting corrupted data: if one insert fails and the others succeed,
you'll have incomplete data in your database. Always include related
modifications in a transaction. And if each call to the database can use
a different connection, then the complete operation, from start to end
of transaction, needs to be done in one call, as transactions are tied
to the connection.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Re: Get next unique ID from a table before insert @@identity / Sequence

(brent.ryan@gma il.com) writes:[color=blue]
> insert into <table>;
> select @@identity;
>
> This doesn't work because the select @@identity might give me the value
> of an insert from someone else's request.[/color]

No, @@identity is local to the connection, so it cannot be someone
else's value. Well, if you submit to queries and close your connection
in between, it won't work, but that would be poor practice anyway.

Hugo's suggestion of using a stored procedure is an excellent idea.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Michael Gray]

Re: Get next unique ID from a table before insert @@identity / Sequence

On Thu, 17 Mar 2005 22:57:45 +0000 (UTC), Erland Sommarskog
<esquel@sommars kog.se> wrote:
[color=blue]
> (brent.ryan@gma il.com) writes:[color=green]
>> insert into <table>;
>> select @@identity;
>>
>> This doesn't work because the select @@identity might give me the value
>> of an insert from someone else's request.[/color]
>
>No, @@identity is local to the connection, so it cannot be someone
>else's value. Well, if you submit to queries and close your connection
>in between, it won't work, but that would be poor practice anyway.
>
>Hugo's suggestion of using a stored procedure is an excellent idea.[/color]

Excuse me for butting in here, Erland, but there is one 'little'
problem that I have found with @@IDENTITY that I can't see referred to
anywhere, and that anyone relying on it should know about, and that is
that @@IDENTITY can return unexpected values in certain circumstances.

In the supplied example:

insert into <table>
select @@identity

BEAWRE!
If there is a trigger fired during the insert on <table>, and the
trigger performs an insert itself, then @@IDENTITY will return the ID
from the Trigger's insert, not the <table> insert.

This caused me many to lose much more hair than I can afford!

It behaves this way in SQL Server 7, and 2000.

Here is a script to create a test data base:
(Make a new blank database, I called it "Test")

=============== ==============
/****** Object: Table [dbo].[MainTable] Script Date: 18/03/2005
3:10:38 PM ******/
CREATE TABLE [dbo].[MainTable] (
[MainTableId] [int] IDENTITY (1, 1) NOT NULL ,
[LongName] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TriggerTable] Script Date: 18/03/2005
3:10:39 PM ******/
CREATE TABLE [dbo].[TriggerTable] (
[TriggerTableId] [int] IDENTITY (666, 1) NOT NULL ,
[TriggerRowLongN ame] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TriggerTable] WITH NOCHECK ADD
CONSTRAINT [PK_TriggerTable] PRIMARY KEY CLUSTERED
(
[TriggerTableId]
) ON [PRIMARY]
GO
/****** Object: Stored Procedure dbo.Test_sp Script Date:
18/03/2005 3:10:39 PM ******/
CREATE PROCEDURE dbo.Test_sp
AS
INSERT INTO MainTable (LongName) VALUES ('TestLongName' )
SELECT @@IDENTITY
GO
/****** Object: Trigger dbo.MainTable_T rigger1 Script Date:
18/03/2005 3:10:39 PM ******/
CREATE TRIGGER MainTable_Trigg er1
ON dbo.MainTable
FOR INSERT,UPDATE,D ELETE
AS
INSERT INTO TriggerTable (TriggerRowLong Name) VALUES ('Stuff')
GO
=============== ==============

Then, if one executes [Test_sp] in Query Analyser,

EXEC Test_sp

the returned @@IDENTITY is not 1, as you would expect, (this is ID of
the new MainTable row), but 666, which is the ID of the row inserted
via the trigger!
(I seeded this table's identity to begin at 666, in order to show up
clearly)

I would be interested if you were aware of this tiny problemette.

[Erland Sommarskog]

Re: Get next unique ID from a table before insert @@identity / Sequence

Michael Gray (fleetg@newsguy .spam.com) writes:[color=blue]
> Excuse me for butting in here, Erland, but there is one 'little'
> problem that I have found with @@IDENTITY that I can't see referred to
> anywhere, and that anyone relying on it should know about, and that is
> that @@IDENTITY can return unexpected values in certain circumstances.
>
> In the supplied example:
>
> insert into <table>
> select @@identity
>
> BEAWRE!
> If there is a trigger fired during the insert on <table>, and the
> trigger performs an insert itself, then @@IDENTITY will return the ID
> from the Trigger's insert, not the <table> insert.[/color]

Yes, this is a correct observation. For this reason, you should use
scope_identity( ) instead. This function was introduced in SQL 2000.

scope_identity( ) returns the most recently generated IDENTITY in the
current scope, that is a trigger, stored procedure, block of dynamic
SQL etc.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp