SQL Server does not have a force_spool hint.
You might be able to hand-"code" an execution plan that includes a spool and then force its use with a plan hint. However, I have not tried that method yet.
Hand coding an execution plan is beyond the scope of this forum. Here are a few hints to get you started:
- The SQL Server execution plan is represented as XML. You can think of it as a complex programming language.
- The XML schema specification explains the syntax of this language: http://schemas.microsoft.com/sqlserver/2004/07/showplan/
- within each execution plan you have a hierarchy of "RelOp" nodes with a single root node. This root node is where the execution starts. The relational operator (RelOp) is executed and requests rows from its child (or children). Those in turn execute to then recursively call their children.
The best way to get started is to read simple XML execution plans.
To get a spool in there you should be just able to inject a spool at any place that makes sense. The execution plan however will be checked by SQL Server to be valid for the query at hand and might get rejected.
Again, this is just a research suggestion and I have not yet tried it myself.