When a #temp table won't do

Sometimes only a view will do.

You know the feeling? You add just one more layer of nesting to your view and suddenly it's just dying so you reach for the temp table.
But that means you'll need to make this a proc. But what if you really REALLY need it to be a view, e.g. it needs to be used by another view.

So, this happens because SQL server always wonders if it can maybe optimise better than just fetching everything in your subqueries. Often it's right, however, sometimes it's wrong and ends up calling different bits of the same subquery thousands of times over like it's got a bad stutter. This is what really kills performance. You need to force it to remember (or "spool") the contents of the subquery. Unfortunately, despite a popular feature request dating back years, microsoft haven't introduced this as a query hint. However here's something that often, if not always, works as a hack:

** Put TOP and ORDER BY clause in the subquery **

The ORDER BY forces it to fetch and remember (spool) the whole thing. The TOP must be there for it to let you use the ORDER BY.
The number you put after TOP just needs to be any huge number that's more rows than you'll ever get. In other words a number high enough to ensure that the TOP does nothing.

SELECT TOP 2000000000 *
FROM Stuff
ORDER BY Something