2

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.

EDIT

This confirms that SQL Server overwrites the variable (proving Randolph West's answer).

enter image description here

vs

enter image description here

  • Be careful to not assign too much weight or meaning to phrases like "last value in the table." Think about a table as a bag of rows - there really isn't any order until you ask for one. – Aaron Bertrand Oct 31 '16 at 18:46
  • Is there a case where the row provided is not the last row of the clustered index? By definition, it is how the table is stored, right? – Dan Andrews Oct 31 '16 at 23:24
2

Under the covers, SQL Server will return the entire rowset from the SELECT, and then assigns every value (really quickly) to the variable. Of course, the last value in the SELECT will be what is finally assigned.

Here's where it gets tricky. The order of what is returned, with no WHERE or ORDER BY clause, can depend on any number of conditions. Usually it's returned in the order of the clustered index, but that's not guaranteed.

  • This is what I thought it was doing. Are there any references to lead us to this conclusion? – Dan Andrews Oct 31 '16 at 18:16
  • I think this proves it select @NEXTID += nextid from generators – Dan Andrews Oct 31 '16 at 18:21
  • The SELECT without a WHERE is going to return 0..n rows, but you're assigning it to a scalar variable, which can only take one value (and that even depends on whether it's nullable). By this logic, when you return a rowset that contains > 0 rows, the last value is what will be assigned to the scalar variable. – Randolph West Oct 31 '16 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.