@@IDENTITY.... @@trancount, @@rowcount

Question

Post reply

@@IDENTITY.... @@trancount, @@rowcount

Barkingdog

SSCoach

Points: 18670
More actions
June 25, 2010 at 9:04 am

#226077

I avoid using @@IDENTITY in t-sql code because it can return unexpected values:
"It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value."
Do @@trancount and @@rowcount suffer from the same issue?
TIA,
Barkingdog

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

To my knowledge, no.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

@@Trancount returns the current transaction count for that connection.

@@rowcount returns the number of rows affected by the last statement that ran on that connection.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 3

Keyword here is connection. These are stable for the connection. @@identity doesn't scope to your connection or that table.

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 4

Steve Jones - Editor (6/25/2010)
Keyword here is connection. These are stable for the connection. @@identity doesn't scope to your connection or that table.

As usual, Pinal Dave has the 411!

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

[font="Courier New"]ZenDada[/font]

noeld SSC Guru Points: 96590 More actions · Answer 5

ZenDada (6/25/2010)

Steve Jones - Editor (6/25/2010)
Keyword here is connection. These are stable for the connection. @@identity doesn't scope to your connection or that table.

As usual, Pinal Dave has the 411!

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/%5B/quote%5D

What is the problem with Books OnLine ?

* Noel

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 6

noeld (6/25/2010)

ZenDada (6/25/2010)
Steve Jones - Editor (6/25/2010)
Keyword here is connection. These are stable for the connection. @@identity doesn't scope to your connection or that table.
As usual, Pinal Dave has the 411!
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/%5B/quote%5D
What is the problem with Books OnLine ?

Not a blog. You miss out on all the fun of... "oh oh... look over there... no over there... there... wow that's cool!"

[font="Courier New"]ZenDada[/font]

noeld SSC Guru Points: 96590 More actions · Answer 7

ZenDada (6/25/2010)

noeld (6/25/2010)
ZenDada (6/25/2010)
Steve Jones - Editor (6/25/2010)
Keyword here is connection. These are stable for the connection. @@identity doesn't scope to your connection or that table.
As usual, Pinal Dave has the 411!
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/%5B/quote%5D
What is the problem with Books OnLine ?
Not a blog. You miss out on all the fun of... "oh oh... look over there... no over there... there... wow that's cool!"

Oh oh oh .... look is regurgitating what's already said in Books online ... I get it

* Noel

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

Also, you can simply return the IDENTITY values inserted in the OUTPUT clause of the INSERT statement.

This is probably the best solution, and the only good solution for the case of a multi-row insert.