asked
need to know @@identity and identity column value difference when inserting a record which gives error
[link text][1]I will execute below step1 script. Created Table with identity column Insert one record, so @@identity displayed as “1”. I have tried to insert one more record which will gives error. **/*step 1*/ create table identitytest ( id int identity(1,1), msg int ) insert into identitytest(msg) select 1 select @@identity insert into identitytest(msg) select 'nikhil'** Because of error record not inserted in the table. I have executed step2 to get result. After getting error also I have checked identity it shows as 1. So error does not effects @@identity. I have tried to insert new record with correct value. But in table identity column value inserted as “3” instead of 2. Why ? **/*step 2*/ select @@identity insert into identitytest(msg) select '2' select * from identitytest drop table identitytest** Please go through attachment to get in detail. [1]: /storage/temp/2904-scripts.sql
You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
[`@@IDENTITY`][1] isn't limited to your query - it's the last IDENTITY value used anywhere in your session (which includes queries fired off by triggers, which you might not be interested in). And the documentation linked above states that IDENTITY fields are affected by failed INSERTS as well as successful ones. You might want to try using [`@@SCOPE_IDENTITY`][2] instead. There's an example of why this might be more suitable in the documentation linked. [1]:
https://msdn.microsoft.com/en-us/library/ms187342.aspx [2]:
https://msdn.microsoft.com/en-us/library/ms190315.aspx
Thanks for quick response Thomas.I have not used any triggers on that table and i have tried with SCOPE_IDENTITY().same result.I want to know why in that table 3rd select inserted identity column value as 3 instead of 2.if it skips 2 because of 2nd select statement then why @@identity not changed.
@@IDENTITY wouldn't change because the error. Nor would @@SCOPE_IDENTITY (the correct one to use as @ThomasRushton says). However, if you look at [DBCC CHECKIDENT][1] you'll find that the identity value has a gap. You can see why this is if you look at the execution plan for your INSERT statement. One of the first things it does is allocate a new identity value. Then it attempts the insert. If it has to rollback the insert, the identity value is lost. [1]:
https://msdn.microsoft.com/en-us/library/ms176057.aspx
19 People are following this question.
