Chapter 05 · Core Querying: Joins, Subqueries, APPLY, CTEs, and Set Operations
SELECT Processing Order, Filtering, Ordering, TOP/OFFSET, and Determinism
Predict SQL Server query results from logical SELECT processing, explicit ordering, TOP/OFFSET semantics, and deterministic pagination before interpreting the physical plan.
Learning outcomes
The ServiceHub operations API needs a page of open work orders.
One developer writes TOP (10), another sorts only
by priority, and a third filters on a SELECT alias in
WHERE. Each query looks plausible, but the team
cannot predict which rows belong to page 2 after two work orders
share the same priority. Before tuning indexes, you need a
reliable model for how a SELECT is logically composed and what
SQL Server actually promises about row ordering.
Explain the logical processing/binding order of a SELECT without confusing it with physical execution-plan order.
Choose WHERE versus HAVING from the row or group being filtered and predict alias visibility.
Use ORDER BY, TOP, WITH TIES, OFFSET and FETCH with explicit determinism requirements.
Design stable pagination with a unique tie-breaker and understand why separate page requests can still drift when data changes.
Inspect plans and row counts as evidence without assuming that a physical operator sequence rewrites SQL semantics.
ServiceHubLab, ops.Technician, and
ops.WorkOrder come from Chapter 01. Chapters
02–04 established instance/database scope, type contracts, and
schema integrity. Chapter 05 now asks a different question:
given valid data, can you predict the result set before trying
to make the query fast?
1. Logical processing is a reasoning model, not a stopwatch
Microsoft documents a logical binding order beginning with
FROM/ON/JOIN, then
WHERE, grouping, HAVING,
SELECT, DISTINCT,
ORDER BY, and finally TOP. This
explains why a SELECT-list alias is normally visible to ORDER BY
but not to WHERE: the alias does not exist at the logical point
WHERE binds. The optimizer is free to choose a physically
different execution plan as long as the result obeys the
language semantics.
USE ServiceHubLab;GO-- Correct: WHERE filters source rows using source expressions.SELECT w.work_order_id, w.priority, CASE WHEN w.closed_at IS NULL THEN 'open' ELSE 'closed' END AS lifecycleFROM ops.WorkOrder AS wWHERE w.closed_at IS NULLORDER BY lifecycle, w.work_order_id;GO-- Deliberately wrong: lifecycle is defined in SELECT, after WHERE binds.SELECT w.work_order_id, CASE WHEN w.closed_at IS NULL THEN 'open' ELSE 'closed' END AS lifecycleFROM ops.WorkOrder AS wWHERE lifecycle = 'open';GOThe second statement should fail with an invalid-column-name error. That failure is useful evidence: SQL Server is not evaluating the query text strictly top-to-bottom. Repair it by filtering on the underlying expression, using a derived table/CTE when reuse genuinely improves composition, or by moving a group predicate to HAVING when it depends on aggregation.
2. WHERE reduces rows; HAVING evaluates groups
WHERE decides which source rows participate before
grouping. HAVING applies a search condition to a
group or aggregate result. Putting every predicate in HAVING can
hide intent and force the grouping step to consider rows that
could have been eliminated earlier. Conversely, an aggregate
such as COUNT(*) is not available to WHERE because
the group has not been formed yet.
USE ServiceHubLab;GOSELECT w.status, COUNT(*) AS work_order_count, AVG(CONVERT(decimal(10,2), w.priority)) AS avg_priorityFROM ops.WorkOrder AS wWHERE w.opened_at >= '2026-08-10T00:00:00'GROUP BY w.statusHAVING COUNT(*) >= 1ORDER BY work_order_count DESC, w.status ASC;GOFor this tiny lab, every surviving group has at least one row; the point is the contract. The date condition is a row predicate, while the count condition is a group predicate. In larger workloads, plans may push or transform predicates when equivalence is safe, but that physical choice does not change the logical meaning you should design first.
3. ORDER BY is the result-order contract
A relational result without an outer ORDER BY has
no guaranteed presentation order. A clustered index, an observed
plan, a small table, or yesterday's output does not create a
language-level guarantee. Even with ORDER BY, if the sort keys
are not unique, tied rows can appear in different relative
positions across executions. For a UI page, add a stable unique
tie-breaker such as work_order_id.
USE ServiceHubLab;GO-- Unstable among rows that share priority/opened_at:SELECT work_order_id, status, priority, opened_atFROM ops.WorkOrderORDER BY priority DESC, opened_at ASCOFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;GO-- Deterministic ordering for a fixed snapshot of data:SELECT work_order_id, status, priority, opened_atFROM ops.WorkOrderORDER BY priority DESC, opened_at ASC, work_order_id ASCOFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;GO
The second query defines a total order because
work_order_id is unique. It still does not freeze
the dataset between independent requests: if rows are inserted,
deleted, or their sort keys change between page 1 and page 2,
offset pagination can shift. Microsoft explicitly notes that
stable OFFSET/FETCH paging also depends on data stability or
appropriate transactional isolation. Later chapters cover
snapshot semantics; here, record that ordering determinism and
cross-request consistency are separate concerns.
4. TOP limits rows; it does not invent an order
TOP (n) without ORDER BY returns an unspecified
subset. Use it for bounded inspection only when any qualifying
rows are acceptable. When the business meaning is “highest
priority,” “newest,” or “cheapest,” encode that meaning in ORDER
BY. WITH TIES deliberately allows more than
n rows when the final ORDER BY value ties, so clients
must not assume the cardinality equals the TOP expression.
USE ServiceHubLab;GOSELECT TOP (2) work_order_id, priority, opened_atFROM ops.WorkOrder;GOSELECT TOP (2) WITH TIES work_order_id, priority, opened_atFROM ops.WorkOrderORDER BY priority DESC;GOSELECT TOP (2) work_order_id, priority, opened_atFROM ops.WorkOrderORDER BY priority DESC, opened_at ASC, work_order_id ASC;GOThe first result is not a deterministic “first two.” The second is deterministic only with respect to which priority values qualify; rows tied on the last priority can expand the result and their relative order is not defined by the priority alone. The third defines exactly which two rows win for a fixed database state.
5. What the execution plan can and cannot prove
An execution plan describes how SQL Server chose to produce a semantically valid result. You might see a Sort, Top, index scan, index seek, aggregate, or a combination; the optimizer can eliminate operators when an access path already provides a useful ordering. Do not read the graphical plan from left to right and substitute that display for logical query processing.
USE ServiceHubLab;GOSET STATISTICS IO ON;SET STATISTICS TIME ON;SELECT TOP (3) w.work_order_id, w.priority, w.opened_atFROM ops.WorkOrder AS wWHERE w.status <> 'cancelled'ORDER BY w.priority DESC, w.opened_at, w.work_order_id;SET STATISTICS TIME OFF;SET STATISTICS IO OFF;GORecord your own logical reads and elapsed/CPU time if you execute the lab. This course does not claim a universal number because table size, cache state, hardware, container limits, statistics, indexes, and concurrency differ. The plan proves the chosen physical strategy for that compilation; it does not make unordered SQL ordered.
6. Failure analysis and production judgment
“TOP 20 is enough because the clustered primary key always gives insertion order.” That relies on an observed access path, not a result-order contract. A new index, parallel plan, statistics change, engine upgrade, or different predicate can return a different subset.
Write the business ranking explicitly in ORDER BY and append a unique tie-breaker. If paging spans independent requests, choose an isolation/keyset strategy that matches the consistency requirement instead of assuming OFFSET alone creates a snapshot.
For production, the mandatory chapter lab assumes SQL Server 2025 CU7 build 17.0.4065.4, compatibility level 170, Developer or Express, and ordinary SELECT permission on the ServiceHub tables. None of the SELECT semantics in this lesson requires Enterprise licensing or a server restart. Tool choice—SSMS, VS Code with MSSQL, or current sqlcmd—does not change the engine semantics, although client display and batch behavior differ.
Check your understanding
- Why can an alias defined in SELECT usually be used by ORDER BY but not WHERE?
- Does a clustered index guarantee output order when the outer query has no ORDER BY?
- Why can TOP (5) WITH TIES return more than five rows?
- What additional property should a pagination ORDER BY usually have?
- Why is logical SELECT processing order different from physical plan operator order?
Review the answers
Because SELECT binds after WHERE but before ORDER BY in the logical processing model.
No. Physical storage/access order is not a result-order guarantee.
Rows tied with the last qualifying ORDER BY value are included by design.
A unique tie-breaker so every row has a deterministic position for a fixed data snapshot.
Logical order defines SQL semantics and name visibility; the optimizer may choose any equivalent physical strategy.
Authoritative references
- SELECT — syntax and logical processing/binding order
- ORDER BY — ordering plus OFFSET/FETCH behavior and paging stability
- TOP — row limiting and WITH TIES semantics
- Execution plans — physical plan evidence
- SQL Server 2025 build versions — servicing baseline