A CommonTableExpression doesn't persist data in any way. It's basically just a way of creating a sub-query in advance of the main query itself.

This makes it much more like an in-line view than a normal sub-query would be. Because you can reference it repeatedly in one query, rather than having to type it again and again.

But it is still just treated as a view, expanded into the queries that reference it, macro like. No persisting of data at all.

This, unfortunately for you, means that you must do the persistance yourself.

• If you want the CTE's logic to be persisted, you don't want an in-line view, you just want a view.

• If you want the CTE's result set to be persisted, you need a temp table type of solution, such as the one you do not like.