Discussion:
SCOPE_IDENTITY vs Transaction scoped Select @@Identity
(too old to reply)
MattC
2005-01-07 13:23:10 UTC
Permalink
Hi,

I have been using the following method for Inserted ID retreival:

BEGIN TRANSACTION

INSERT ......

SELECT @@IDENTITY

COMMIT TRANSACTION

would I be better off using:

INSERT ...

SELECT SCOPE_IDENTITY()


TIA


MattC
Dan Guzman
2005-01-07 13:39:54 UTC
Permalink
Yes, it's better to use SCOPE_IDENTITY() in SQL 2000 so that the value is
not affected by triggers.
--
Hope this helps.

Dan Guzman
SQL Server MVP
Post by MattC
Hi,
BEGIN TRANSACTION
INSERT ......
COMMIT TRANSACTION
INSERT ...
SELECT SCOPE_IDENTITY()
TIA
MattC
MattC
2005-01-07 13:56:41 UTC
Permalink
Should I still encapsulate within a Transaction?
Post by Dan Guzman
Yes, it's better to use SCOPE_IDENTITY() in SQL 2000 so that the value is
not affected by triggers.
--
Hope this helps.
Dan Guzman
SQL Server MVP
Post by MattC
Hi,
BEGIN TRANSACTION
INSERT ......
COMMIT TRANSACTION
INSERT ...
SELECT SCOPE_IDENTITY()
TIA
MattC
Dan Guzman
2005-01-07 14:28:48 UTC
Permalink
Post by MattC
Should I still encapsulate within a Transaction?
The returned identity value will be the same with or without the
transaction.
--
Hope this helps.

Dan Guzman
SQL Server MVP
Post by MattC
Should I still encapsulate within a Transaction?
Post by Dan Guzman
Yes, it's better to use SCOPE_IDENTITY() in SQL 2000 so that the value is
not affected by triggers.
--
Hope this helps.
Dan Guzman
SQL Server MVP
Post by MattC
Hi,
BEGIN TRANSACTION
INSERT ......
COMMIT TRANSACTION
INSERT ...
SELECT SCOPE_IDENTITY()
TIA
MattC
Louis Davidson
2005-01-07 20:19:03 UTC
Permalink
One interesting thing:

drop table testIdentity
go
create table testIdentity
(
id int identity
)

insert into testIdentity default values

select scope_identity()

begin transaction

insert into testIdentity default values
select scope_identity()

rollback transaction

select * from testIdentity
select scope_identity()


The value returned by scope_identity() is unaffected by the rollback
transaction. Just a little warning, unless I am missing something :)
--
----------------------------------------------------------------------------
Louis Davidson - ***@hotmail.com
SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
Post by Dan Guzman
Post by MattC
Should I still encapsulate within a Transaction?
The returned identity value will be the same with or without the
transaction.
--
Hope this helps.
Dan Guzman
SQL Server MVP
Post by MattC
Should I still encapsulate within a Transaction?
Post by Dan Guzman
Yes, it's better to use SCOPE_IDENTITY() in SQL 2000 so that the value
is not affected by triggers.
--
Hope this helps.
Dan Guzman
SQL Server MVP
Post by MattC
Hi,
BEGIN TRANSACTION
INSERT ......
COMMIT TRANSACTION
INSERT ...
SELECT SCOPE_IDENTITY()
TIA
MattC
Dan Guzman
2005-01-08 16:14:15 UTC
Permalink
Post by Louis Davidson
The value returned by scope_identity() is unaffected by the rollback
transaction. Just a little warning, unless I am missing something :)
Louis, you are right that SCOPE_IDENTITY() is not affected by a rollback.
It will reflect the last identity value inserted regardless of whether or
not the transaction is subsequently rolled back. Like other variable
values, SCOPE_IDENTITY() is not affected by transaction scope.
--
Hope this helps.

Dan Guzman
SQL Server MVP
Post by Louis Davidson
drop table testIdentity
go
create table testIdentity
(
id int identity
)
insert into testIdentity default values
select scope_identity()
begin transaction
insert into testIdentity default values
select scope_identity()
rollback transaction
select * from testIdentity
select scope_identity()
The value returned by scope_identity() is unaffected by the rollback
transaction. Just a little warning, unless I am missing something :)
--
----------------------------------------------------------------------------
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
Post by Dan Guzman
Post by MattC
Should I still encapsulate within a Transaction?
The returned identity value will be the same with or without the
transaction.
--
Hope this helps.
Dan Guzman
SQL Server MVP
Post by MattC
Should I still encapsulate within a Transaction?
Post by Dan Guzman
Yes, it's better to use SCOPE_IDENTITY() in SQL 2000 so that the value
is not affected by triggers.
--
Hope this helps.
Dan Guzman
SQL Server MVP
Post by MattC
Hi,
BEGIN TRANSACTION
INSERT ......
COMMIT TRANSACTION
INSERT ...
SELECT SCOPE_IDENTITY()
TIA
MattC
Loading...