Regarding Identity column value retreival
-
Hi,
I am working on a .NET project for order manangement system. There will be multiple users accessing the application simultaneously. We have primary key columns as identity columns in most of our tables in our database. When we create new records, in our SPs we are doing an insert and to get the value of the Primary Key immediately after the insert, we are retrieving the last inserted identity value and using it in the rest of the procedure.
Which one is the best to retrieve the value of the record just inserted - @@Identity, Scope_Identity OR Ident_Current
We should have the right column value considering there will be multiple inserts in the tables precisely at the same, say, micro second. There should be no overlaps where the id value generated for one user/session etc should not be retrieved for another user or session etc.
Please let us know what is the best to use in our condition.
Thanks
oursmp
-
use SCOPE_IDENTITY( )
According to books online :
- IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
- @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
- SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
Check BOL for more info.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Interestingly you need to check BOL for IDENT_CURRENT to find out, why SCOPE_IDENTITY() is mostly preferable to @@IDENTITY
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply