Why doesn't this query use an index spool?
As you know, the optimizer's search is not exhaustive. It tries things that make sense in context, and which frequently pay dividends on real queries. Forcing a loop join between two single-column unindexed heap tables is not such a scenario. That said, here are some details:

SQL Server likes to transform applies to joins early, because it knows more tricks with joins. Later on, it may explore converting the join back to an apply. The difference between the two being correlated parameters (outer references). Applies make sense when there is a suitable index on the inner side. Your example has no indexes, so the optimizer is not persuaded to explore translation to an apply.

A simple (non-apply) join has the join predicate on the join operator instead of outer references. The spool optimization for a non-apply is typically a lazy table spool, since there is no predicate on the inner side, only at the join.

The optimizer does not consider building an index on the fly to enable an apply; rather the sequence of events is usually the reverse: transform to apply because a good index exists.

You can sometimes encourage an apply rather than a join by using APPLY syntax in your query. The undocumented trace flag 9114 can assist in this by dissuading the optimizer from translating a logical apply to a join up front. For example:

SELECT * 
FROM dbo.X_1000 AS a
CROSS APPLY (SELECT * FROM dbo.X_1000 AS b WHERE b.ID = a.ID) AS b
OPTION (QUERYTRACEON 9114);




An index spool is favoured for apply because the outer reference means the selection is applied on the inner side of the join. You will often see this via SelToIndexOnTheFly but other paths exist. See my article The Eager Index Spool and The Optimizer.