Forcing an index spool

I know its something that should be avoided for performance reasons, but am trying to show a condition where it appears as a demo on how to make sure it does not appear.

However, I end up with a missing index warning, yet the optimizer chooses not to create a temporary index.

The query I am using is

SELECT 
    z.a
FROM dbo.t5 AS z WITH(INDEX(0))
WHERE 
    EXISTS 
    (
        SELECT y.a 
        FROM dbo.t4 AS y
        WHERE y.a = z.a
    )
OPTION (MAXDOP 1);

Table schemas are:

CREATE TABLE dbo.t4
(
    a   integer NULL,
    b   varchar(1000) NULL,
    p   varchar(100) NULL
);

CREATE TABLE dbo.t5
(
    a   integer NULL,
    b   varchar(1000) NULL
);

CREATE UNIQUE CLUSTERED INDEX c1 
ON dbo.t5 (a);

Both tables have 10,000 rows, which you can simulate with:

UPDATE STATISTICS dbo.t4 
WITH 
    ROWCOUNT = 10000, 
    PAGECOUNT = 1000;

UPDATE STATISTICS dbo.t5 
WITH 
    ROWCOUNT = 10000,
    PAGECOUNT = 1000;

The query plan is:

```

One way to get an index spool to appear naturally is to express the requirement using slightly different syntax:

SELECT DISTINCT 
    z.a
FROM dbo.t5 AS z
JOIN dbo.t4 AS y ON
    y.a >= z.a AND y.a <= z.a
OPTION (LOOP JOIN, MAXDOP 1, FORCE ORDER);

This produces an execution plan like:

Rewriting the equality as a pair of equivalent inequalities encourages the use of an index spool, though the spooled predicate is not exactly what you were after, the semantics are ultimately the same.

Another way is to abuse TOP:

SELECT 
    z.a
FROM dbo.t5 AS z
WHERE 
    EXISTS 
    (
        SELECT TOP ((SELECT 100)) PERCENT y.a 
        FROM dbo.t4 AS y
        WHERE y.a = z.a
    );

There is no easy way to introduce the desired index spool using the original syntax; however, that's not to say it is impossible. Since you only need this for a demo, and will not be using this anywhere near a production system, I will show you another way:

SELECT 
    z.a
FROM dbo.t5 AS z WITH(INDEX(0))
WHERE 
    EXISTS 
    (
        SELECT y.a 
        FROM dbo.t4 AS y
        WHERE y.a = z.a
    )
OPTION (MAXDOP 1, LOOP JOIN, QUERYTRACEON 9114);

The execution plan is:

The index spool predicate is as desired:
Seek Keys[1]: Prefix: [dbo].[t4].a = [dbo].[t5].[a] as [z].[a]


You will not be able to use this plan in a USE PLAN hint because the optimizer would not normally consider it.

Further reading:

• Nested Loops Joins and Performance Spools

• CROSS APPLY produces outer join

StackExchange Database Administrators Q#30158, answer score: 22