Chapter 06 · Advanced T-SQL: Windows, Aggregation, PIVOT, MERGE Alternatives, and JSON
GROUP BY, GROUPING SETS, ROLLUP, CUBE, and Advanced Aggregation
Produce explainable subtotal and multidimensional reports with GROUPING SETS, ROLLUP, CUBE, GROUPING, and GROUPING_ID while controlling grain and workspace growth.
Learning outcomes
ServiceHub finance receives three independently written reports:
totals by region, totals by service category, and a grand total.
The reports disagree because one query excludes rows with an
unknown region while another turns NULL into the
word “Total.” A fourth report uses several
UNION ALL branches and scans the same fact table
repeatedly. Advanced aggregation solves the shape problem only
when you understand output grain, subtotal-generated
NULL placeholders, and the combinatorial growth of
grouping sets.
Build ordinary GROUP BY queries from a clearly stated output grain before adding subtotal operators.
Use GROUPING SETS, ROLLUP, and CUBE for exact subtotal shapes and predict the groups each construct creates.
Distinguish source NULL values from subtotal placeholders with GROUPING and GROUPING_ID.
Reason about cardinality, memory grants, sorts/hash aggregates, and spill risk without claiming one aggregation strategy is always faster.
Choose advanced aggregation only when its result contract is clearer than repeated UNION branches.
GROUP BY changes grain. GROUPING SETS asks SQL Server to produce several grains in one logical query. ROLLUP is hierarchical; CUBE generates all combinations. The syntax is compact, but the result can be much larger than the base grouping.
1. Start from one explicit grain
USE ServiceHubLab;GOIF SCHEMA_ID(N'lab06') IS NULL EXEC(N'CREATE SCHEMA lab06 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab06.InvoiceLine;GOCREATE TABLE lab06.InvoiceLine( line_id bigint NOT NULL CONSTRAINT PK_lab06_InvoiceLine PRIMARY KEY, region varchar(20) NULL, service_category varchar(30) NOT NULL, amount decimal(12,2) NOT NULL);INSERT lab06.InvoiceLine(line_id,region,service_category,amount) VALUES(1,'North','repair',125.00),(2,'North','install',300.00),(3,'South','repair',175.00),(4,'South','inspection',90.00),(5,NULL,'repair',80.00),(6,'North','repair',50.00);GOSELECT region, service_category, SUM(amount) AS revenueFROM lab06.InvoiceLineGROUP BY region, service_categoryORDER BY region, service_category;GO
The ordinary query produces one row per distinct
(region, service_category). The
NULL region is a real source value meaning “region
not recorded,” not a grand-total marker. Preserve that
distinction when subtotals are added.
2. GROUPING SETS expresses the exact grains you need
GROUPING SETS lets you name several grouping
shapes, including the empty grouping set () for a
grand total. It is logically similar to a
UNION ALL of separate GROUP BY queries, but the
optimizer can consider the entire request together. That does
not guarantee one scan or one aggregate operator; inspect the
plan rather than asserting an implementation.
USE ServiceHubLab;GOSELECT region, service_category, SUM(amount) AS revenue, GROUPING(region) AS g_region, GROUPING(service_category) AS g_category, GROUPING_ID(region, service_category) AS grouping_levelFROM lab06.InvoiceLineGROUP BY GROUPING SETS( (region, service_category), (region), (service_category), ())ORDER BY GROUPING_ID(region,service_category), region, service_category;GO
GROUPING(column) returns 1 when the column is being
used as an aggregate placeholder for that output row and 0 when
the value belongs to the grouping key. Therefore the real source
region IS NULL detail row has
g_region = 0, while a subtotal that aggregates all
regions has g_region = 1. That bit is the safe way
to label totals.
COALESCE(region, All regions) cannot distinguish
a real missing region from a subtotal placeholder. It silently
collapses two different meanings into the same display label.
3. ROLLUP is hierarchical; CUBE is combinatorial
ROLLUP(a,b,c) produces detail groups plus
progressively higher prefixes: (a,b,c),
(a,b), (a), and (). This
suits hierarchies such as year → month or region → category when
the listed order expresses the drill-up path.
CUBE(a,b) produces all combinations:
(a,b), (a), (b), and
(). With many dimensions, the number of grouping
sets grows quickly; do not CUBE a dozen dimensions merely
because the syntax is short.
USE ServiceHubLab;GOSELECT region, service_category, SUM(amount) AS revenue, GROUPING_ID(region,service_category) AS gidFROM lab06.InvoiceLineGROUP BY ROLLUP(region, service_category)ORDER BY gid, region, service_category;GOSELECT region, service_category, SUM(amount) AS revenue, GROUPING_ID(region,service_category) AS gidFROM lab06.InvoiceLineGROUP BY CUBE(region, service_category)ORDER BY gid, region, service_category;GOThe CUBE adds category-only totals that the ROLLUP does not. If the report does not consume those rows, generating them is wasted work and can enlarge memory, sorting, serialization, and network costs.
4. Label subtotal rows from metadata, not from NULL guessing
USE ServiceHubLab;GOSELECT CASE WHEN GROUPING(region)=1 THEN 'ALL REGIONS' WHEN region IS NULL THEN 'UNKNOWN REGION' ELSE region END AS region_label, CASE WHEN GROUPING(service_category)=1 THEN 'ALL CATEGORIES' ELSE service_category END AS category_label, SUM(amount) AS revenueFROM lab06.InvoiceLineGROUP BY GROUPING SETS ((region,service_category),(region),())ORDER BY GROUPING_ID(region,service_category), region, service_category;GOThe display now preserves the business difference between “unknown source value” and “subtotal over all values.” Keep the raw grouping flags in machine-facing outputs when downstream consumers need to identify levels programmatically.
5. Advanced aggregation can request real workspace
Large grouping operations commonly use Stream Aggregate or Hash Match (Aggregate), and they may require sorting or hashing memory. CUBE/large grouping-set requests can multiply output groups and increase memory pressure. A spill is not proven by the presence of GROUPING SETS; it is observed in an actual plan/runtime warning and related diagnostics. Compare estimated versus actual rows, memory grant information, and tempdb spill evidence at representative scale.
USE ServiceHubLab;GOSET STATISTICS IO ON;SELECT region, service_category, SUM(amount) AS revenueFROM lab06.InvoiceLineGROUP BY GROUPING SETS ((region,service_category),(region),(service_category),());SET STATISTICS IO OFF;GOOn six rows this is intentionally tiny. Do not infer production speed from the lab. Later chapters scale the ServiceHub dataset for plan, memory-grant, and tempdb analysis.
6. Production judgment
Write the list of required output grains first. Use GROUPING SETS for an exact list, ROLLUP for a true hierarchy, and CUBE only when every dimensional combination is a required result. Carry GROUPING/GROUPING_ID so downstream code can distinguish subtotal placeholders from real NULL values.
The mandatory lab needs only SQL Server 2025 Developer/Express
and compatibility level 170. No restart or special edition
feature is required. Monitor result cardinality, memory grants,
spills, repeated scans, and client serialization costs when
moving to large dimensions. Rollback is dropping
lab06.InvoiceLine. If a simpler set of separate
queries is operationally clearer—for example, independently
cached dashboards—clarity can outweigh syntactic compactness.
7. Compare semantic alternatives, not just shorter syntax
Advanced aggregation is most valuable when it makes a multi-grain requirement easier to audit. Before replacing several existing report queries, compare not only execution plans but also security predicates, filters, currency/time-zone rules, NULL policies, and snapshot timing. Separate UNION branches may have drifted because each branch acquired slightly different logic; GROUPING SETS can centralize those shared predicates. The reverse is also possible: independently refreshed dashboards might intentionally have different freshness or permissions, in which case forcing them into one giant aggregation can couple unrelated workloads.
For a production review, write acceptance rows for each grouping level before looking at the plan. Verify detail totals reconcile to the requested subtotals, verify real NULL source values remain distinguishable, and test that adding a new region/category does not change the meaning of existing grouping IDs. Then inspect actual cardinality, grant size, spills, parallelism, and elapsed time under representative scale. A shorter query is useful only when its semantics and operating model are also clearer.
Check your understanding
- What grain does GROUP BY region, service_category produce?
- How does GROUPING(region) distinguish a subtotal NULL from a real NULL region?
- What extra grouping does CUBE(region,category) produce beyond ROLLUP(region,category)?
- Why can CUBE become expensive with many dimensions?
- Does GROUPING SETS guarantee a single physical scan?
Review the answers
One row per distinct region/category combination.
It returns 1 when region is a subtotal placeholder and 0 when region is part of the grouping key, even if the source value itself is NULL.
It additionally produces category-only totals.
The number of grouping combinations grows combinatorially, increasing output and possible workspace requirements.
No. It expresses logical groupings; the optimizer chooses the physical implementation.
Authoritative references
- GROUP BY — GROUPING SETS, ROLLUP, CUBE, limits, and semantics
- GROUPING — distinguishing subtotal placeholders from source NULLs
- GROUPING_ID — grouping-level bitmaps
- Execution plans — aggregate and memory evidence
- SQL Server 2025 build versions — servicing baseline