CTEs, Recursive Queries, UNION/INTERSECT/EXCEPT, and Composable Query Design

Learning outcomes

The final Chapter 05 task combines reusable query stages, a service-zone hierarchy, and comparison of two work-order sets. The team wants CTEs because they “materialize intermediate results,” recursion because it is concise, and UNION everywhere “to be safe.” Those assumptions can create needless duplicate-elimination work or runaway recursion. A common table expression is primarily a named query expression; set operators have explicit duplicate/type semantics; recursion needs a termination model and guardrail.

Lab bootstrap: ensure the set-comparison sources exist

Lesson 5 can run after the earlier lessons or directly after the base ServiceHubLab. The bootstrap creates only missing Chapter 05 tables and leaves existing evidence intact.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab05') IS NULL EXEC(N'CREATE SCHEMA lab05 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab05.WorkOrderVisit',N'U') IS NULLBEGIN    CREATE TABLE lab05.WorkOrderVisit    (visit_id int IDENTITY(1,1) NOT NULL CONSTRAINT PK_lab05_Visit PRIMARY KEY,     work_order_id bigint NOT NULL, visit_started_at datetime2(0) NOT NULL, outcome varchar(20) NOT NULL);    INSERT lab05.WorkOrderVisit(work_order_id,visit_started_at,outcome) VALUES    (1001,'2026-08-10T10:00:00','inspection'),(1001,'2026-08-10T11:30:00','follow-up'),    (1002,'2026-08-10T11:00:00','replacement'),(1004,'2026-08-11T09:30:00','calibration');END;IF OBJECT_ID(N'lab05.WorkOrderTag',N'U') IS NULLBEGIN    CREATE TABLE lab05.WorkOrderTag    (work_order_id bigint NOT NULL, tag varchar(20) NOT NULL,     CONSTRAINT PK_lab05_Tag PRIMARY KEY(work_order_id,tag));    INSERT lab05.WorkOrderTag(work_order_id,tag) VALUES    (1001,'pump'),(1001,'urgent'),(1001,'north'),(1002,'sensor'),(1004,'meter');END;GO

1. A CTE names a query expression for one statement

A nonrecursive CTE exists for the single statement that follows its WITH clause. Microsoft explicitly notes that CTE query results are not automatically materialized; an outer query that references the CTE can cause the underlying definition to be re-executed. SQL Server may still introduce a spool or another materialization-like physical operator when the optimizer judges it useful, but that is a plan choice, not a CTE guarantee.

USE ServiceHubLab;GO;WITH OpenOrders AS(    SELECT w.work_order_id, w.technician_id, w.priority, w.opened_at    FROM ops.WorkOrder AS w    WHERE w.closed_at IS NULL),Assigned AS(    SELECT o.work_order_id, o.priority, o.opened_at,           t.technician_code, t.region_code    FROM OpenOrders AS o    JOIN ops.Technician AS t      ON t.technician_id = o.technician_id)SELECT *FROM AssignedORDER BY priority DESC, opened_at, work_order_id;GO

The semicolon before WITH safely terminates a previous statement in a batch. The CTEs improve naming and decomposition, but they do not create indexes, statistics, or independent persisted storage. If an intermediate result truly needs reuse with separate indexing/statistics or needs to survive across statements, consider a temporary table and measure the tradeoff.

2. Recursive CTEs iterate from anchor rows until no new rows return

A recursive CTE has one or more anchor members and at least one recursive member. SQL Server runs the anchor to form T0, repeatedly runs the recursive member against the prior result, and stops when the recursive member returns no rows. The last anchor and first recursive member are joined with UNION ALL. Recursive hierarchy columns must align in count and data type.

USE ServiceHubLab;GODROP TABLE IF EXISTS lab05.ServiceZone;CREATE TABLE lab05.ServiceZone(    zone_id int NOT NULL CONSTRAINT PK_lab05_ServiceZone PRIMARY KEY,    parent_zone_id int NULL,    zone_name varchar(40) NOT NULL);INSERT lab05.ServiceZone(zone_id,parent_zone_id,zone_name) VALUES(1,NULL,'Global'),(10,1,'North'),(20,1,'West'),(11,10,'North-City'),(12,10,'North-Rural'),(21,20,'West-City');GO;WITH ZoneTree AS(    SELECT z.zone_id, z.parent_zone_id, z.zone_name, 0 AS depth    FROM lab05.ServiceZone AS z    WHERE z.zone_id = 1    UNION ALL    SELECT c.zone_id, c.parent_zone_id, c.zone_name, p.depth + 1    FROM lab05.ServiceZone AS c    JOIN ZoneTree AS p      ON c.parent_zone_id = p.zone_id)SELECT zone_id, parent_zone_id, zone_name, depthFROM ZoneTreeORDER BY depth, zone_idOPTION (MAXRECURSION 20);GO

MAXRECURSION is a query hint that limits recursion depth (0 means no limit; positive values are bounded). It is a guardrail, not a substitute for a correct termination predicate or acyclic data model. Choose a bound from the expected domain depth and operational safety requirement rather than copying 100 or 0 blindly.

3. Deliberately create a cycle and observe the guardrail

Real hierarchies can become cyclic through bad imports or missing constraints. If Global points under one of its descendants, a traversal that expects a tree can loop. This disposable lab injects a cycle and uses a low MAXRECURSION so the statement terminates with an error rather than consuming unbounded work.

USE ServiceHubLab;GOUPDATE lab05.ServiceZoneSET parent_zone_id = 11WHERE zone_id = 1;GO;WITH ZoneTree AS(    SELECT zone_id, parent_zone_id, zone_name, 0 AS depth    FROM lab05.ServiceZone    WHERE zone_id = 1    UNION ALL    SELECT c.zone_id, c.parent_zone_id, c.zone_name, p.depth + 1    FROM lab05.ServiceZone AS c    JOIN ZoneTree AS p ON c.parent_zone_id = p.zone_id)SELECT * FROM ZoneTreeOPTION (MAXRECURSION 5);GO-- Repair the disposable hierarchy.UPDATE lab05.ServiceZoneSET parent_zone_id = NULLWHERE zone_id = 1;GO

The expected failure is a recursion-limit error. That proves the guardrail fired; it does not identify the business cause automatically. Validate hierarchy invariants during ingestion or with controlled cycle-detection logic when the domain requires a tree/DAG.

4. UNION ALL concatenates; UNION also removes duplicates

UNION ALL combines compatible result sets and preserves duplicates. UNION applies distinct set semantics to the combined rows, which usually requires additional work such as sorting or hashing. Use UNION when duplicate elimination is part of the result definition, not as defensive formatting. Corresponding columns must be type-compatible; resulting names come from the first query.

USE ServiceHubLab;GOSELECT customer_codeFROM ops.WorkOrderWHERE status IN ('new','assigned')UNION ALLSELECT customer_codeFROM ops.WorkOrderWHERE priority >= 2;GOSELECT customer_codeFROM ops.WorkOrderWHERE status IN ('new','assigned')UNIONSELECT customer_codeFROM ops.WorkOrderWHERE priority >= 2;GO

A customer satisfying both branches can appear twice with UNION ALL and once with UNION. Neither is universally “better”; one preserves bag/multiset multiplicity while the other asks for a mathematical-set-like result. Inspect plans to see the actual duplicate-elimination strategy chosen.

5. INTERSECT and EXCEPT express overlap and difference

INTERSECT returns distinct rows present in both inputs. EXCEPT returns distinct rows from the left input that are absent from the right input. For these set operators, NULL values compare as equal for duplicate/set determination, which differs from ordinary = NULL three-valued logic. Column count and compatible types must align, and data-type precedence/collation rules can affect the result contract.

USE ServiceHubLab;GO-- Work orders that both have a visit and have a tag.SELECT work_order_id FROM lab05.WorkOrderVisitINTERSECTSELECT work_order_id FROM lab05.WorkOrderTag;GO-- Work orders with visits but no tags.SELECT work_order_id FROM lab05.WorkOrderVisitEXCEPTSELECT work_order_id FROM lab05.WorkOrderTag;GO

These formulations are often clearer than a manual join when the business question is literally set overlap/difference. They return distinct rows. If multiplicity or child detail matters, a join or EXISTS pattern may represent the requirement better.

6. Plan evidence: a spool is observed, not promised

USE ServiceHubLab;GOSET STATISTICS IO ON;;WITH VisitCounts AS(    SELECT work_order_id, COUNT(*) AS visit_count    FROM lab05.WorkOrderVisit    GROUP BY work_order_id)SELECT a.work_order_id, a.visit_count, b.visit_count AS repeated_referenceFROM VisitCounts AS aJOIN VisitCounts AS b ON b.work_order_id = a.work_order_idORDER BY a.work_order_id;SET STATISTICS IO OFF;GO

Capture the actual plan and look for scans, aggregates, joins, and any spool. If a spool appears, it is evidence that this optimizer chose to cache/intermediate some rows for this plan. If it does not, that does not make the CTE invalid. When repeated expensive references matter, compare a temp-table alternative with measured reads/CPU and realistic scale.

7. Chapter lab cleanup, production judgment, and bridge

USE ServiceHubLab;GODROP FUNCTION IF EXISTS lab05.VisitSummary_MSTVF;DROP FUNCTION IF EXISTS lab05.VisitForWorkOrder;DROP TABLE IF EXISTS lab05.ServiceZone;DROP TABLE IF EXISTS lab05.BlockedCustomer;DROP TABLE IF EXISTS lab05.WorkOrderTag;DROP TABLE IF EXISTS lab05.WorkOrderVisit;GOIF SCHEMA_ID(N'lab05') IS NOT NULL   AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE schema_id = SCHEMA_ID(N'lab05'))    EXEC(N'DROP SCHEMA lab05;');GO

Chapter 05 finishes the core query-composition layer: ordering and row limiting define deterministic presentation; joins define row combinations; EXISTS/IN/subqueries define existence and scalar contracts; APPLY handles lateral table expressions; CTEs and set operators compose larger queries. Chapter 06 builds on this foundation with window functions, advanced aggregation, PIVOT, safe data-change patterns, and JSON workloads.

Production prerequisites remain SQL Server 2025 CU7 build 17.0.4065.4, compatibility level 170 for the declared lab baseline, Developer/Express, and permissions to create/drop the disposable lab objects. No paid edition, cloud service, restart, or retired Azure Data Studio dependency is required.

Authoritative references

• WITH common_table_expression — CTE scope and non-materialization guidance
• Recursive CTEs — anchor/recursive semantics and MAXRECURSION
• UNION — UNION versus UNION ALL semantics
• EXCEPT and INTERSECT — set difference/intersection and comparison semantics
• Execution plans — physical implementation evidence
• SQL Server 2025 build versions — servicing baseline