Difference between?
-
1) Declare @i int=0
select @i=id+1 from user;
select @i
/* here id is an identity column*/
2) select @i=max(id)+1 from user;
select @i;
In the above queries gives the same output but is there any difference in terms of performance?
-
ravi@sql (3/26/2013)
In the above queries gives the same outputNot necessarily. For example, just tried it on one of my tables and there's > 500 difference.
The first one gives the last id returned + 1 (there's no order by clause so no guarantee it's the id you think it is).
The second the maximum id in the table + 1.
The equivalent would be:
select @i=id+1 from user order by id;
I'd reckon using MAX as in the second query will be quicker though.
-
ravi@sql (3/26/2013)
1) Declare @i int=0select @i=id+1 from user;
select @i
/* here id is an identity column*/
2) select @i=max(id)+1 from user;
select @i;
In the above queries gives the same output but is there any difference in terms of performance?
These do NOT give the same result. It may appear that they do but the reality is quite different. Obviously the second query will ALWAYS return the largest value + 1. The first query however will not. What in fact the first query is doing is it will assign the value from the last row in the result set. Since it is an identity it will most likely be the largest value. The problem is that you basically counting on an order by from a clustered index. This is not going to work forever. You could use the first query IF you added an order by clause.
Hands down the query to use the aggregate query. The performance difference will be unnoticeable in a small table but once the table gets bigger it will become more apparent.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
They certainly don't give the same output. The first assumes an implied order that does not exist and hence will return a value one higher than the identity value of some or other row in the table.
Run on a table with 50000 rows (the ID column runs sequentially, without gaps, 1..50000)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Oh, and as for performance.
Query 1 (the one that doesn't work right)
Table 'PrimaryTable_Medium'. Scan count 1, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 35 ms.
Query 2 (the one that gives the correct results)
Table 'PrimaryTable_Medium'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Sean Lange (3/26/2013)
What in fact the first query is doing is it will assign the value from the last row in the result set. Since it is an identity it will most likely be the largest value.Assuming that the clustered index is on the identity column and that there's no smaller nonclustered index that contains the identity column, which can't both be true unless there are no nonclustered indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thank you very much for your valuble reply.
Regards,
ravi@sql
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply