You can generate an Index Spool easily with an unindexed Tally table:

SELECT N

INTO #Tally

FROM dbo.Numbers (50000);

GO

SELECT COUNT(*)

FROM #Tally T1

JOIN #Tally T2

ON T2.N < T1.N;

That uses an in-line TVF I keep handy to generate a temporary tally heap - you can use any method that generates a similar object.

Index spools are not all that rare, but the estimated cost of spooling the rows to a worktable, creating the index, and dropping it afterward is quite high, so the optimiser will often choose a different approach, purely on cost grounds (usually a hash join - the example above avoids that by joining on an inequality only).

There are two types of index spool - eager and lazy. The above example generates an eager spool - one that consumes all rows from its input before it can be used to seek with.

A lazy index spool is a bit more like a regular table spool - except it never truncates its worktable (a normal spool only ever caches one result - it truncates its worktable on every rebind).

A lazy index spool accumulates results on every rebind (when the correlated parameter changes). Personally, I'd love a way to be able to hint the use of lazy index spools, but that's another story.

Interestingly, you cannot trust the reported numbers for rebinds and rewinds for an index spool - the internals report a rewind to anything other than the last value seen as a rebind rather than the rewind it actually is. Sad, but true.