@@identity vs Scope_Identity()
-
I just had a question about the difference between @@identity and scope_identity(). I'm in the process of debugging and improving the performance of a stored procedure. Someone else wrote it and used a bunch of cursors with @@identity FROM MyTable type of syntaxes.
I know you should pretty much never use @@identity in favor of scope_identity because it a global variable and it's not scope sesative so if you add triggers down the road etc. etc.
What I didn't know is that when you do SELECT @MyVar = @@identity from MyTable the engine does an index scan on the clustered index. Depending on the size of the table that could be a significant encumberance to the execution of your procedure.
The worst part is that it's in a cursor so it executes roughly 20-30 times every time you run the procedure. You can see how this compounds the problem, but I digress.
The question is does Scope_identity cause any kind of index scan or whatnot? I've replaced the above code with SELECT @MyVar = scope_identity(). I don't even see it listed in the Execution plan when I run the procedure. I was expecting to see something like a constant scan.
The proceure is executing much faster now although I still have a few cursors to get rid of, so I know that this helped greatly, but I was just wondering why it doesn't show in the execution plan.
-
That would seem to answer it to me.
No step in the plan and faster code. That would almost seem like a no brainer except for the fact that I've never encountered this request. I sure would love to have some time to test this.
-
Doh!!!!!!!
Select @Something = SomeCol FROM dbo.MyTable
There's no where condition in this query, the server will HAVE TO SCAN to query the results. I think the programmer tried to invent a feature that just wasn't there at the time!!!!
That explains the difference. As you stated, I think your safer with SCOPE_IDENTITY(), and I would use that one, right after I dropped those cursors
. -
Thanks for confirming my suspicions. I was mostly just curious that it didn't appear as a step in the new execution plan as I assumed it would have, because it was still doing work. Assigning a value to @Something.
thanks Again.
-Luke.
-
Ninja,
I found something like that in a production system, selecting @@identity from SomeTable right after an insert.
I almost had to choke somebody.
-
Yeah the code I'm working on has been in production 5 years or so... And it's not like it was upgraded from SQL 7 hence the @@ identity. It's always been on SQL 2000. Working to improve the performance of this system has been a bear, there's just so much to fix...
-
I had missed this one too. Select @SomeVar from dbo.SomeTable is a valid sql statement that will scan the table. I just thaught that this was a feature I had never heard of!!!!
-
I'm just trying to figure out why anyone would do that. I mean, what's the purpose behind selecting a variable out of a table?
Not that I haven't seen some strange ways of finding the identity value of something just inserted before....
-
I doesnt work that way. Well, it works, but you are just selecting the scalar value for each row of the table.
Now, which one of the 24 million identical values should we use.....
-
Always the last one
. -
Here's a question : What happens if during that select, the @@identity value is changed by another process? Does the plan consider it to be a scalar value that will never be changed, or does it reread it somehow on every row?
-
I'm pretty sure that the @@identity value is connection-specific, so I don't think that it can be changed in the middle of a statement that doesn't preform an insert.
I'm not sure if it actually looks up the @@identity value for each row.
If I do Select @@Identity from Table (34 million rows) I get the select taking 0%, the compute scalar taking 2% and the CI scan takes the other 98%
If I do Select 1+1 from Table, I get the select taking 2% and the CI scan 98%.
Interesting.
-
In the 2nd plan, I guess that the computing is done previously to the query and that it doesn't need to be redone afterwards.
But that's still and interesting point!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply