Strange Expectations
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.
Indexing
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!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
The upcoming PostgreSQL 12 is adding the ability to hint (non-)materialized CTEs. In PG, CTEs have worked the opposite way they do in SQL Server – they are *always* materialized.
The new default is to inline when the CTE is used once in the main query, and materialize when the CTE is referenced multiple times. They also added syntax to override the default: “WITH x AS MATERIALIZED” or “WITH x AS NOT MATERIALIZED”.
Seems like a good option to have!
Yeah! I saw that. I wish I was smart enough to read that code to see how it’s implemented behind the scenes.
I’ve been thinking for a while now that a materialisation hint for CTEs would be a nice feature. You can end up with a whole bunch of code just for temp table management sometimes, even for really simple stuff (plus I continue to see people who *know* that CTEs aren’t materialised stack them like performance-killing pancakes). I like the idea of incorporating inline index definitions.
Hahaha, yeah, it’s so much fun to think about. It’s less fun not to have when it would solve a big problem.