Many-to-Many Merge Join and the Worktable in STATISTICS IO

Internal Worktable reads can appear even when your script creates no temporary table. A many-to-many merge join can need internal storage to replay duplicate-key matches. The operator's properties help connect that work to the data relationship.

Why a Many-to-Many Merge Join Replays Duplicate Groups

A merge join consumes inputs ordered by their join keys. When both inputs can have repeated keys, every matching row on one side must pair with every matching row on the other. SQL Server can store a duplicate group and replay it as the other group's rows are processed.

I check the join relationship before investigating Worktable reads. The internal storage can be doing correct, necessary work for a requested many-to-many result. Two left rows and three right rows for the same key require six matching pairs. Removing some of those pairs would change the query's meaning.

That multiplicative relationship is separate from duplicate complete rows. Every row can have a unique identifier while the join column repeats. A primary key on the row identifier does not guarantee uniqueness of the join key. Inspect the exact columns used by the join.

Build Two Ordered Non-Unique Inputs

The sample tables have distinct row identifiers and repeated MatchKey values. Nonclustered indexes supply ordering by the match column. Included values let the indexes cover the selected detail. Use a disposable database session and keep the temporary tables together.

CREATE TABLE #MergeLeft
(LeftID int PRIMARY KEY,MatchKey int NOT NULL,LeftValue int NOT NULL);
CREATE TABLE #MergeRight
(RightID int PRIMARY KEY,MatchKey int NOT NULL,RightValue int NOT NULL);
INSERT #MergeLeft VALUES(1,10,100),(2,10,200),(3,20,300);
INSERT #MergeRight VALUES(11,10,1),(12,10,2),(13,10,3),(21,20,4);
CREATE INDEX IX_MergeLeft_Key ON #MergeLeft(MatchKey) INCLUDE(LeftValue);
CREATE INDEX IX_MergeRight_Key ON #MergeRight(MatchKey) INCLUDE(RightValue);

The indexes are not declared unique on MatchKey because the sample data is not unique there. A declaration would be false and the database would reject it. Do not delete valid detail rows merely to make an index creation succeed.

Inspect the Forced Many-to-Many Merge Join

Enable the actual execution plan and STATISTICS IO. A MERGE JOIN hint makes the algorithm explicit for this teaching example. The index hints encourage the available ordered access paths. These hints are diagnostic controls, not recommendations for application deployment.

SET STATISTICS IO ON;
SELECT l.LeftID,r.RightID,l.MatchKey,l.LeftValue,r.RightValue
FROM #MergeLeft l WITH(INDEX(IX_MergeLeft_Key))
INNER MERGE JOIN #MergeRight r WITH(INDEX(IX_MergeRight_Key))
 ON r.MatchKey=l.MatchKey
ORDER BY l.MatchKey,l.LeftID,r.RightID;
SET STATISTICS IO OFF;

Open the Merge Join properties and inspect Many to Many. Read the actual matching pairs as well. The output should represent the requested combinations, not one arbitrary right row per left row. Record the observed property rather than inferring it only from the join hint.

STATISTICS IO can report Worktable activity for this internal processing. Small inputs can show zero reads or other implementation differences. Do not invent a nonzero count to support the explanation. The operator property describes the relationship capability even when the small group's measured reads are modest.

Confirm the Key Multiplicity Directly

Count rows per join key on both sides. This explains the expected pair count and separates genuine cardinality from a malformed join condition. If a multicolumn relationship is intended, omitting a component can create accidental extra combinations.

SELECT MatchKey,COUNT_BIG(*) AS LeftMatches
FROM #MergeLeft GROUP BY MatchKey HAVING COUNT_BIG(*)>1;
SELECT MatchKey,COUNT_BIG(*) AS RightMatches
FROM #MergeRight GROUP BY MatchKey HAVING COUNT_BIG(*)>1;

A count greater than one is not automatically bad data. Payments, order lines, and repeated observations naturally have this shape. Decide whether the report needs the detail combinations, a summary per key, or only existence. That result-grain decision belongs before tuning the physical join.

Declare Uniqueness Only Where It Is True

A separate lookup table can represent one descriptive row per MatchKey. Its data is genuinely unique. Check the condition, then enforce it with a unique index. This demonstration answers a different lookup question than the earlier detail-to-detail join; it is not an equivalent replacement for all those pairs.

CREATE TABLE #MergeLookup(MatchKey int NOT NULL,CategoryValue int NOT NULL);
INSERT #MergeLookup VALUES(10,7),(20,8);
SELECT MatchKey,COUNT_BIG(*) AS Matches
FROM #MergeLookup GROUP BY MatchKey HAVING COUNT_BIG(*)>1;
CREATE UNIQUE INDEX UX_MergeLookup_Key ON #MergeLookup(MatchKey)
INCLUDE(CategoryValue);
SET STATISTICS IO ON;
SELECT l.LeftID,l.MatchKey,k.CategoryValue
FROM #MergeLookup k WITH(INDEX(UX_MergeLookup_Key))
INNER MERGE JOIN #MergeLeft l WITH(INDEX(IX_MergeLeft_Key))
 ON l.MatchKey=k.MatchKey;
SET STATISTICS IO OFF;

Inspect Many to Many again. The join hint also fixes the join order, so the unique lookup listed first becomes the outer input. A unique outer input lets SQL Server skip duplicate-group replay, and the property reads False. List #MergeLeft first instead and the property returns to True, because the outer input then repeats its keys. Other operators in the complete query can still use internal worktables, so the entire STATISTICS IO report is not guaranteed to contain no Worktable line.

I verify both the data and the constraint before claiming uniqueness. A current absence of duplicates is weaker than an enforced business key. Which relationship is supposed to remain unique when tomorrow's rows arrive?

Compare a Hash Join With the Many-to-Many Merge Join

A hash join can be a better physical choice for some unsorted or large inputs. It also has memory and spill considerations. Compare it with the same detailed result contract rather than using the unique lookup result as a misleading faster substitute.

SET STATISTICS IO ON;
SELECT l.LeftID,r.RightID,l.MatchKey,l.LeftValue,r.RightValue
FROM #MergeLeft l
INNER HASH JOIN #MergeRight r ON r.MatchKey=l.MatchKey;
SET STATISTICS IO OFF;

For deployment, remove demonstration hints and review the optimizer's unforced choice. Sorted indexes can benefit other queries, but their maintenance has a cost. Do not keep a forced merge merely because the sample lesson discusses that algorithm.

Keep Internal Storage Evidence in Context

Worktable does not mean your script secretly created a user temporary table. It describes internal work that can come from joins, sorts, spools, and other operators. Link the line to the full plan and runtime properties before assigning a cause.

A small worktable can be an efficient way to produce valid matches. A large duplicate group can generate substantial replay work and a large output. Evaluate both the internal processing and the amount of data the caller actually needs. An enormous requested result remains expensive under another join algorithm.

The temporary tray is not stealing rows; it is keeping enough rows available to produce every requested pair. Once the relationship and required output are correct, compare algorithms and supporting indexes using representative data. That gives the worktable a concrete explanation and the proposed tuning a defensible result contract.

A uniqueness check must use the complete join key. A lookup unique on MatchKey and RegionID is not unique on MatchKey alone. If the query omits RegionID, the optimizer cannot use that composite guarantee as a single-column guarantee. Fix the intended relationship first. Adding an unrelated unique row identifier does not eliminate duplicate match groups or their replay requirement.

Keep all compared plans with their input sizes and result counts. A change in join cardinality is a semantic change, even when it removes internal storage. The report should identify that difference clearly before presenting any read or duration improvement.

A many-to-many merge join preserves the complete matching combinations requested by the query. Check the result grain before replacing a many-to-many merge join with a different relationship.

Related reading on this blog: Change Join Type for Query and Scan Count Zero for Statistics IO.

A merge worktable is not proof of an accidental temp table, it is internal support for matching duplicate groups correctly.

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