Right here we have now two related queries utilizing
the place the
SELECT clause incorporates some expressions calculated in aggregation:
SELECT RN10, RN10 / 10, COUNT FROM ( SELECT RN, RN/10 AS RN10, RN/100 AS RN100 FROM ( SELECT RN = -1 + ROW_NUMBER() OVER (ORDER BY 1/0) FROM grasp..spt_values ) A ) B GROUP BY GROUPING SETS ((RN10), (RN10 / 10), ()) ORDER BY 1, 2
it is plan is right here: first question plan
SELECT RN10, SUBSTRING(RN,3,99), COUNT FROM ( SELECT RN, SUBSTRING(RN,2,99) AS RN10 FROM ( SELECT RN = CAST(-1 + ROW_NUMBER() OVER (ORDER BY 1/0) AS VARCHAR(99)) FROM grasp..spt_values ) A ) B GROUP BY GROUPING SETS ((RN10), (SUBSTRING(RN,3,99)), ()) ORDER BY 1, 2
the corresponding plan is right here: second question plan
Each the queries first calculate some expression for aggregation,
RN10 / 10 within the first case and
SUBSTRING(RN,3,99) within the second, then the identical expression is used within the
SELECT clause however as the primary plan reveals it is re-calculated within the first question and it isn’t within the second.
Because the end result we have now
NULLs within the first end result set that’s fairly unexpectedly:
Can somebody clarify why the primary question makes the calculation 2 occasions (one in aggregation and another time within the ultimate
choose) whereas the second makes it one time solely?