A lot of people still expect odd things from CTEs.
- Performance fences
- Cached results
There’s no clue in how they’re written that you won’t get those.
I’ve gone back and forth on whether or not this would be worthwhile. It totally could be, but it’d have to be pretty thoughtful.
Materialization vs. Fencing
The difference here is subtle but necessary. Right now, people will use TOP, which sets a row goal, and provides some logical isolation of the query in your CTE.
The problem remains that if that CTE is referenced via join > 1 time, the internal syntax is re-run each time.
Even if your query is fenced off, it is not materialized.
Fencing could leverage existing NOEXPAND hints, but materialization would likely require a new hint that performed the equivalent of SELECT… INTO #t, and then replaced references to the CTE alias with a pointer to the temporary object.
One appeal of temp tables is that there is additional indexing flexibility, so any syntax would have to allow existing inline index syntax of temp tables to be used.
In other words, an index that may not make sense on a real table given your existing workload might make sense on a temp table. Or like, if a temp table is the result of joining two tables together, there could be a compound index you could create on the temp table that’s otherwise impossible to create.
Next feature request: multi-table indexes 😂
Thanks for reading!