CROSS/OUTER APPLY, Table-Valued Functions, and Lateral-Style Query Patterns

Learning outcomes

The ServiceHub API needs each work order plus its latest visit, or NULL visit columns when no visit exists. A scalar subquery can return one column, but the API needs visit ID, timestamp, and outcome together. A teammate proposes a cursor; another creates a multi-statement table-valued function and assumes SQL Server will estimate it like a normal query. APPLY gives a relational way to evaluate a table expression in the context of each left row, while function shape determines how much optimization information the engine can use.

Lab bootstrap: preserve Chapter 05 continuity without hidden dependencies

The APPLY examples need deterministic visit rows. This setup is safe to rerun and creates them only when Lesson 2 has not already done so.

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;GO

1. APPLY lets the right table source reference the left row

Microsoft defines APPLY so the right table source is evaluated in the context of each row from the left table source. CROSS APPLY returns a left row only when the right side produces rows. OUTER APPLY preserves the left row and null-extends the right columns when the right side is empty. This resembles a lateral join in other SQL dialects.

USE ServiceHubLab;GOSELECT w.work_order_id,       w.status,       latest.visit_id,       latest.visit_started_at,       latest.outcomeFROM ops.WorkOrder AS wOUTER APPLY(    SELECT TOP (1)           v.visit_id, v.visit_started_at, v.outcome    FROM lab05.WorkOrderVisit AS v    WHERE v.work_order_id = w.work_order_id    ORDER BY v.visit_started_at DESC, v.visit_id DESC) AS latestORDER BY w.work_order_id;GO

Work orders without visits remain visible because OUTER APPLY null-extends the right side. Replace it with CROSS APPLY and those work orders disappear. The TOP is deterministic because the right-side ORDER BY includes a unique visit ID tie-breaker.

2. APPLY is not synonymous with row-by-row procedural execution

The syntax describes a dependency: the right expression may reference the current left row. The optimizer can still transform, reorder, or implement the query using relational operators when legal. Do not infer “one function call per row” merely from the text. Use the actual execution plan and runtime evidence to see what happened for the compiled statement.

USE ServiceHubLab;GOSELECT COUNT_BIG(*) AS cross_apply_rowsFROM ops.WorkOrder AS wCROSS APPLY(    SELECT TOP (1) v.visit_id    FROM lab05.WorkOrderVisit AS v    WHERE v.work_order_id = w.work_order_id    ORDER BY v.visit_started_at DESC, v.visit_id DESC) AS x;SELECT COUNT_BIG(*) AS outer_apply_rowsFROM ops.WorkOrder AS wOUTER APPLY(    SELECT TOP (1) v.visit_id    FROM lab05.WorkOrderVisit AS v    WHERE v.work_order_id = w.work_order_id    ORDER BY v.visit_started_at DESC, v.visit_id DESC) AS x;GO

The second count should equal the number of work orders in the left input; the first equals only work orders for which the right expression found a visit. That row-preservation rule is semantic and independent of the physical join operator chosen later.

3. Inline TVFs are parameterized table expressions

An inline table-valued function (iTVF) returns the result of one SELECT statement. It has no table return variable. This shape gives the optimizer visibility into the relational expression, so predicates from the outer query can often be optimized together with the function definition. An iTVF is useful when a parameterized relational expression is reused and its abstraction remains understandable.

USE ServiceHubLab;GOCREATE OR ALTER FUNCTION lab05.VisitForWorkOrder(@work_order_id bigint)RETURNS TABLEASRETURN(    SELECT v.visit_id, v.visit_started_at, v.outcome    FROM lab05.WorkOrderVisit AS v    WHERE v.work_order_id = @work_order_id);GOSELECT w.work_order_id, v.visit_id, v.outcomeFROM ops.WorkOrder AS wCROSS APPLY lab05.VisitForWorkOrder(w.work_order_id) AS vORDER BY w.work_order_id, v.visit_id;GO

This function returns all matching visits, so the query grain is work-order × visit. If the requirement is one latest visit, keep the TOP/order logic in the table expression or define an appropriately named iTVF whose contract is explicit.

4. Multi-statement TVFs create an optimization boundary—but modern SQL Server can interleave

A multi-statement table-valued function (MSTVF) populates a table return variable across multiple statements. Historically, SQL Server used fixed cardinality guesses for MSTVFs, which could produce poor plans. Microsoft documents a fixed guess of 100 from SQL Server 2014, but starting with SQL Server 2017 and compatibility level 140, eligible queries can use interleaved execution: execution pauses optimization, materializes the MSTVF result to learn its cardinality, and resumes optimization with that information. Therefore “MSTVFs always estimate 100 rows” is obsolete as a universal statement.

USE ServiceHubLab;GOCREATE OR ALTER FUNCTION lab05.VisitSummary_MSTVF(@work_order_id bigint)RETURNS @r TABLE(    visit_count int NOT NULL,    latest_visit_at datetime2(0) NULL)ASBEGIN    INSERT @r(visit_count, latest_visit_at)    SELECT COUNT(*), MAX(v.visit_started_at)    FROM lab05.WorkOrderVisit AS v    WHERE v.work_order_id = @work_order_id;    RETURN;END;GOSELECT w.work_order_id, s.visit_count, s.latest_visit_atFROM ops.WorkOrder AS wCROSS APPLY lab05.VisitSummary_MSTVF(w.work_order_id) AS sORDER BY w.work_order_id;GO

At compatibility 170, interleaved execution is available in principle, but eligibility and plan shape must be observed. Do not claim it fired just because the database level is high enough. Inspect the actual plan and relevant database-scoped configuration. More importantly, ask whether the MSTVF is needed at all; base-table joins or an iTVF often expose more relational structure to optimization.

5. Observe database prerequisites and plan evidence

USE ServiceHubLab;GOSELECT compatibility_levelFROM sys.databasesWHERE name = DB_NAME();SELECT name, value, value_for_secondaryFROM sys.database_scoped_configurationsWHERE name = 'DISABLE_INTERLEAVED_EXECUTION_TVF';SELECT o.name, o.type_descFROM sys.objects AS oWHERE o.object_id IN(    OBJECT_ID(N'lab05.VisitForWorkOrder'),    OBJECT_ID(N'lab05.VisitSummary_MSTVF'));GOSET STATISTICS IO ON;SELECT w.work_order_id, s.visit_countFROM ops.WorkOrder AS wCROSS APPLY lab05.VisitSummary_MSTVF(w.work_order_id) AS sORDER BY w.work_order_id;SET STATISTICS IO OFF;GO

If you capture the actual plan, record estimated and actual rows around the TVF and whether interleaved execution affected compilation. Query Store and IQP are covered later; Chapter 05 only requires that you stop using version-obsolete cardinality folklore as a design argument.

6. Failure analysis, cleanup, and production judgment

APPLY and T-SQL TVFs are available in the free local Developer/Express path used here. Creating functions requires appropriate database DDL permission. No restart or paid topology is needed. Monitor actual row counts, compilation behavior, CPU/reads, function use, and plan regressions before standardizing an abstraction.

Authoritative references

• FROM, JOIN and APPLY — CROSS/OUTER APPLY semantics
• CREATE FUNCTION — inline and multi-statement TVF syntax
• Create user-defined functions — TVF optimization guidance and MSTVF cardinality history
• Intelligent Query Processing details — interleaved execution prerequisites/behavior
• Execution plans — physical-plan evidence
• SQL Server 2025 build versions — servicing baseline