What Would Materialized CTEs Look Like In SQL Server?

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.


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!

4 thoughts on “What Would Materialized CTEs Look Like In SQL Server?

  1. 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!

  2. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *