I'm asking this question in order to better understand the optimizer's behavior and to understand the limits around index spools. Suppose that I put integers from 1 to 10000 into a heap:
CREATE TABLE X_10000 (ID INT NOT NULL); truncate table X_10000; INSERT INTO X_10000 WITH (TABLOCK) SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master..spt_values t1 CROSS JOIN master..spt_values t2;
And force a nested loop join with
SELECT * FROM X_10000 a INNER JOIN X_10000 b ON a.ID = b.ID OPTION (LOOP JOIN, MAXDOP 1);
This is a rather unfriendly action to take towards SQL Server. Nested loop joins often aren't a good choice when both tables don't have any relevant indexes. Here's the plan:
The query takes 13 seconds on my machine with 100000000 rows fetched from the table spool. However, I don't see why the query has to be slow. The query optimizer has the ability to create indexes on the fly through index spools. This query seems like it would be a perfect candidate for an index spool.
The following query returns the same results as the first one, has an index spool, and finishes in less than a second:
SELECT * FROM X_10000 a CROSS APPLY (SELECT TOP (9223372036854775807) b.ID FROM X_10000 b WHERE a.ID = b.ID) ca OPTION (LOOP JOIN, MAXDOP 1);
This query also has an index spool and finishes in less than a second:
SELECT * FROM X_10000 a INNER JOIN X_10000 b ON a.ID >= b.ID AND a.ID <= b.ID OPTION (LOOP JOIN, MAXDOP 1);
Why doesn't the original query have an index spool? Is there any set of documented or undocumented hints or trace flags that will give it an index spool? I did find this related question, but it doesn't fully answer my question and I can't get the mysterious trace flag to work for this query.