2

I've read that one good reason to use a CTE is to as Microsoft says:

Enable grouping by a column that is derived from a scalar sub-select, or a function that is either not deterministic or has external access.

So I tried this example using SQL 2014 and Adventure Works 2012 DB and it worked!

SELECT DateDiff(D,H.OrderDate,GETDATE()), Count(*)
FROM   Sales.SalesOrderHeader H
GROUP BY DateDiff(D,H.OrderDate,GETDATE())
ORDER BY DateDiff(D,H.OrderDate,GETDATE())

Which doesn't seem right, as GETDATE() is a not deterministic!

So really two questions:

  1. Any one know why this example works, it seems it shouldn't.
  2. Does anyone have a good example of a similar query where a CTE helps

overcome the "group by non-determnistic function" limitation?

Thanks!

rrauenza
  • 6,285
  • 4
  • 32
  • 57
Kris W
  • 21
  • 3
  • I dont see any CTE there so not sure how is related to the example. CTE is just a nice way of write subquery so is easy to read, but there isnt really diference bettween non recursive-CTE and subquery. http://stackoverflow.com/questions/706972/difference-between-cte-and-subquery?rq=1 – Juan Carlos Oropeza Jun 21 '16 at 16:39
  • 1
    `declare @now datetime = getdate()`. In a time consuming query getdate() may change by the moment it `group by` and `order by` – Alex Kudryashev Jun 21 '16 at 16:51
  • 1
    @AlexKudryashev that is not correct. GETDATE() will return the exact same value for any single query. The engine does not recalculate that function over and over in a single query. It will vary between statements in a procedure but not within one query. – Sean Lange Jun 21 '16 at 18:28
  • 1
    How long does it take to write your own sample non-deterministic function? And every example with "CTE" will look exactly the same as one with the _magic_ "SUBQUERY" feature. @AlexKudryashev that's not actual since mssql2005 if I'm not mistaken. – Ivan Starostin Jun 21 '16 at 18:46
  • So I remember the issue since 2005 and didn't think about it since then ;( – Alex Kudryashev Jun 21 '16 at 18:48

0 Answers0