1

For example, I have this query

SELECT @param = column from table

What value is pulled into @param?

I tried this and can't figure out the value that is being pulled. It is not the old record or newer one.

CDspace
  • 2,639
  • 18
  • 30
  • 36
Anurag
  • 78
  • 9

2 Answers2

4

The documentation states:

the variable is assigned the last value that is returned

But without a WHERE clause that uniquely identifies a row nor an ORDER BY clause that specifies a unique value for ordering, the row chosen for the variable assignment is undefined and not deterministic when the table has more than one row.

You could add ORDER BY to the query to return the last ordered row. A more efficient method to do that would to be use SELECT TOP(1)...ORDER BY...DESC. Conversely, SELECT TOP(1)...ORDER BY...ASC will return the first ordered row. Again, the order by column(s) need to be unique for a deterministic value.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Even with `WHEN` it could be more than 1 row. With `ORDER BY` it will be predictable. – PM 77-1 Nov 17 '17 at 22:12
  • @PM77-1 Why would it be predictable with an `ORDER BY`? – Matt Gibson Nov 17 '17 at 22:13
  • @MattGibson - It *could* be predictable as long as the order used uniquely identifies the last row. – PM 77-1 Nov 17 '17 at 22:17
  • @PM77-1, consider `SELECT @param = column from table ORDER BY SomeOtherColumn`, Multiple rows with the same `SomeOtherColumn' value but with a different `column` value would be unpredictable. – Dan Guzman Nov 17 '17 at 22:20
  • @PM77-1 I understand that that's almost certainly what SQL Server will do, but I'm not entirely convinced that it *has* to do that, i.e. that the behaviour is guaranteed. – Matt Gibson Nov 17 '17 at 22:21
  • Please see my clarification. – PM 77-1 Nov 17 '17 at 22:22
  • Thanks for the link to the specifics in the documentation. While I'm generally very happy with MS's documentation, I always seem to find this particular kind of detail hard to track down myself... – Matt Gibson Nov 18 '17 at 10:15
  • Thanks for the answers. I know that we have to use Where, order by to get the desired result. Just curious to know what is the result without any filter conditions. – Anurag Nov 18 '17 at 23:22
  • @Anurag, the result with is undefined in that case. You may get a different results depending on the indexes defined on the table, the session isolation level, or whether SQL Server chooses to use a parallel plan. – Dan Guzman Nov 18 '17 at 23:38
0

This is the value in the column referenced. It seems like it should have a TOP 1 in it, with a WHERE Clause designed to fetch 1 row only.

DanielG
  • 1,669
  • 1
  • 12
  • 26