SQL Server Execution Plan Quiz: One Table, Two Operators

This SQL Server execution plan quiz asks how one table reference in a SELECT statement can produce join and parallelism operators. Inspect the physical accesses and exchange operators to explain why.

A current test with one table reference

I tested this example on SQL Server 2025, using a temporary table containing one million made-up rows. Its setup uses GENERATE_SERIES, available from SQL Server 2022 with compatibility level 160 or later.

Run all four blocks in the same query window so the local temporary table remains available. Enable the actual execution plan before running the two SELECT statements. The setup drops any older #PlanQuiz first, so you can run it again.

-- Demo setup, SQL Server 2022 or later.
-- GENERATE_SERIES requires compatibility level 160 or later.
DROP TABLE IF EXISTS #PlanQuiz;
CREATE TABLE #PlanQuiz
(
    RowId int NOT NULL PRIMARY KEY CLUSTERED,
    GroupId int NOT NULL,
    Amount int NOT NULL,
    Padding char(200) NOT NULL
);
INSERT #PlanQuiz(RowId, GroupId, Amount, Padding)
SELECT value, value % 10, value % 1000, REPLICATE('x', 200)
FROM GENERATE_SERIES(1, 1000000, 1);
CREATE INDEX IX_PlanQuiz_GroupId ON #PlanQuiz(GroupId);

A Physical Join for a Key Lookup

The nonclustered index finds qualifying row identifiers but does not contain Amount. SQL Server retrieves that column from the clustered index through a Key Lookup. The observed plan combines those accesses with a Nested Loops inner join.

SELECT SUM(CONVERT(bigint, Amount)) AS TotalAmount
FROM #PlanQuiz WITH (INDEX(IX_PlanQuiz_GroupId))
WHERE GroupId = 7 AND RowId < 1000
OPTION (MAXDOP 1, RECOMPILE);

This statement names #PlanQuiz once and contains no explicit JOIN clause. In the test, it returned TotalAmount = 50200 and showed Nested Loops with Key Lookup. The physical join describes the access strategy, rather than a second table reference written in the statement.

A Join and Observed Parallelism

The second query reads more qualifying rows by removing the RowId limit. The test plan contained both Nested Loops with Key Lookup and Parallelism (Gather Streams). The actual execution used degree of parallelism 4 and returned TotalAmount = 50200000.

SELECT SUM(CONVERT(bigint, Amount)) AS TotalAmount
FROM #PlanQuiz WITH (INDEX(IX_PlanQuiz_GroupId))
WHERE GroupId = 7
OPTION (MAXDOP 4, RECOMPILE);

The index hint deliberately exposes the lookup strategy for this diagnostic example. MAXDOP 4 sets a ceiling; it does not guarantee a parallel plan. The observed result depends on the data, indexes, estimates, server settings and resources available during execution.

The screenshots show SSMS displaying the actual plans from my test server.

Remove the demo table after the test

Drop #PlanQuiz when you are done. No instance-wide setting or service state is changed. On another instance, inspect your actual plan before claiming the bonus conditions were met.

DROP TABLE #PlanQuiz;

Why COUNT or DISTINCT alone does not guarantee parallelism

COUNT(*) or DISTINCT queries may use parallelism, but the query text alone does not establish that they will. Read the observed execution plan for the required operators. In this example, the second statement demonstrably meets both conditions, while the first isolates the join explanation. Both use a single table reference in a single SELECT statement.

Plans hold more than the query text shows, so open them and look.

One table reference is not one operator, it is a plan that can hide joins and exchanges.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.