@@identity not working after SQL Server 2005 upgrade
After our server upgrade to SQL Server 2005, some of the stored procedures stopped working. The reason, the change in behavior of the @@identity function in 2005:
The value of @@identity is not restricted by current scope and can be affected, for example, by triggers. I.e. if original 'insert' deals with table T1, and the table T1 has a trigger that inserts rows into table T2, then after the original insert is done the @@identity will show new value for the table T2 (not for the T1 as might be expected!!!)
The suggested solution is using SCOPE_IDENTITY()function instead of @@identity. This function IS restricted by the scope and for the example above it will return new identity value for the original table T1 (not for T2 or any other table affected by triggers).
It seems to me that SELECT MAX(T1) FROM T1 is not the best solution, because T1 (and any other) table can be updated simultaneously from multiple sessions and the maximal value is not always guaranteed.
If you have merge replication going on, be aware that now replication uses triggers that were not present in 2000:
For instance - insert trigger MSmerge_ins_5D17CE9EBD2142E3ADC630C47F017946 which does insert rows into other system table with identity column!!! We didn't see these triggers in SQL 2000 database which may explain why the @@identity worked fine in the same code under SQL 2000.
Hope this helps and happy upgrading!
PG
The value of @@identity is not restricted by current scope and can be affected, for example, by triggers. I.e. if original 'insert' deals with table T1, and the table T1 has a trigger that inserts rows into table T2, then after the original insert is done the @@identity will show new value for the table T2 (not for the T1 as might be expected!!!)
The suggested solution is using SCOPE_IDENTITY()function instead of @@identity. This function IS restricted by the scope and for the example above it will return new identity value for the original table T1 (not for T2 or any other table affected by triggers).
It seems to me that SELECT MAX(T1) FROM T1 is not the best solution, because T1 (and any other) table can be updated simultaneously from multiple sessions and the maximal value is not always guaranteed.
If you have merge replication going on, be aware that now replication uses triggers that were not present in 2000:
For instance - insert trigger MSmerge_ins_5D17CE9EBD2142E3ADC630C47F017946 which does insert rows into other system table with identity column!!! We didn't see these triggers in SQL 2000 database which may explain why the @@identity worked fine in the same code under SQL 2000.
Hope this helps and happy upgrading!
PG
Labels: SQL Server stuff
posted by Lizet Pena de Sola at
3:27 PM
6 Comments:
Thanks for your detailed description myself and my colleagues recently upgraded to MSQL 2005 and had to integrate into a payroll system. I was a strenuous, but educating task.
Hello!
Thanks for the valuable information about the @@IDENTITY. I haven't yet migrated to SQL 2005 and I'm scared to death doing it. I manage a data warehouse for a quite large company from where I extract data into som 25+ cubes and reports.
The Select Max() could be used within transactions to lock the table down even though it might not be the neatest solution.
The reason for landing on your blog was a search I made since I'm almost forced to store images in fields. I have an idea that I'm considering. How about performing a base64 encoding into plain ASCII and store that in a varchar field? Have you tried that?
Best Regards
//B Hahn, Sweden
We're still in the process of upgrading our network servers and so far we have gotten a few unexpected behaviors. The main problem was with merge replication, as we added additional parameterized row filters to one of our publications and we started losing data. The cause seemed to be related to double updates on a table, the agent created a conflict and the server always ended up overwriting the row on the client and losing the data.
We are in the process of isolating this behavior to have it documented. For now, we removed that extra filtering.
Bengt,
Regarding the subject of storing images on the database, I believe using varbinary(MAX) would be a better option. Why are you considering to encode the binary data, then store it? The varchar(MAX) and varbinary(MAX) have the same capabilities (indexable fields etc.) plus you're not doing text searches on the varchar fields if you store images inside.
Thanks for your comments!
This is the official report for this bug:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165
The fix is not there yet, not on the SQL Server 2008 CTP of July anyways.
The @@IDENTITY intrinsic behaved like that in SQL 2000 as well; the problem you are seeing is due to the new triggers used for merge replication in 2005.
Definitely use SCOPE_IDENTITY() -- it works great!
Matt
It also happens if you have other triggers on the table, not only replication triggers.
Post a Comment
<< Home