We have a control table called Generators which has a NextID and varchar label. I found some bad code in a stored procedure which assigns a value w/o a where clause - something like below.
declare @NEXTID int select top 1 @NEXTID = nextid from generators order by genname desc select @NEXTID -- result is 511 which is the last value in the table select @NEXTID += nextid from generators select @NEXTID -- result is a summation of all fields/rows select @NEXTID = nextid from generators select @NEXTID -- result is 511 which is the last value in the table select * from generators where nextid = 511 select * from generators -- confirms that this is the last row
It is clear that the @NEXTID gets set to the last item in the clustered index. What isn't clear to me is, "what exactly did SQL server just do?" Profiler doesn't show me much, but what I want to know is, did @NEXTID get set for every row and then get overwritten or did the compiler realize what was happening and went right to the LAST value? Basically this:
select top 1 @NEXTID = nextid from generators order by genname desc
PS: I didn't create the data model.
This confirms that SQL Server overwrites the variable (proving Randolph West's answer).