Right here we have now two related queries utilizing grouping units
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
and
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 NULL
s 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?