4 ms·

I ran into something similar while working with a Seattle-based email vendor years ago. They had an online survey application which recognized your user informa

by biot 14y ago

I ran into something similar while working with a Seattle-based email vendor years ago. They had an online survey application which recognized your user information by a special token, created a new entry in a table for your survey, then proceeded to display your information. In launching a high-volume campaign, people started complaining that they were seeing other people's contact information. They never admitted it, but I'm 100% sure that the issue was that (in SQL Server) they were doing: select @@identity Rather than the correct call of: select scope_identity() The former retrieves the identity of the latest record entered into the table regardless of what SQL statement produced the record (such as another user connecting to the database from a different connection thread); the latter retrieves the identity of the record entered from the current session.

Johngibb 14y ago
Are you sure about that? I am almost positive that you're wrong, and @@identity is per connection. Otherwise how woul it know which table to return an identity for? I thought that the difference had to do with what's returned if a trigger does an insert or something like that...
- mey 14y ago
  @@identity is per session, but can return other things if you are using triggers (since the trigger will happen before your retrieval of @@identity). One more reason I hate triggers (we also use scope_identity only). More fun @ http://msdn.microsoft.com/en-us/library/ms187342.aspx http://msdn.microsoft.com/en-us/library/ms187342.aspx
- biot 14y ago
  You're right. As mey pointed out via the docs, @@identity is for the current session, but may return unexpected results if a trigger fires as part of the statement(s) or if you are using replication. It's almost never what you actually wanted whereas scope_identity() is. Come to think of it, perhaps the vendor I mentioned was using ident_current('tablename') which returns the last identity generated for the specified table name regardless of session and scope; this would explain the resulting behavior of the app.
  - Johngibb 14y ago
    Yep, that'd do it!

by biot 14y ago

Johngibb 14y ago

Are you sure about that? I am almost positive that you're wrong, and @@identity is per connection. Otherwise how woul it know which table to return an identity for? I thought that the difference had to do with what's returned if a trigger does an insert or something like that...

mey 14y ago
@@identity is per session, but can return other things if you are using triggers (since the trigger will happen before your retrieval of @@identity). One more reason I hate triggers (we also use scope_identity only). More fun @ http://msdn.microsoft.com/en-us/library/ms187342.aspx http://msdn.microsoft.com/en-us/library/ms187342.aspx
biot 14y ago
You're right. As mey pointed out via the docs, @@identity is for the current session, but may return unexpected results if a trigger fires as part of the statement(s) or if you are using replication. It's almost never what you actually wanted whereas scope_identity() is. Come to think of it, perhaps the vendor I mentioned was using ident_current('tablename') which returns the last identity generated for the specified table name regardless of session and scope; this would explain the resulting behavior of the app.
- Johngibb 14y ago
  Yep, that'd do it!