CODEWITHSUNDEEP
sqlsql-server
Solonotix
Solonotix

Reputation: 471

Do CTE's materialize a computation?

I've written a UDF for re-use in an ETL cleansing process and am trying to quantify which approach is better. I'm wondering if my assumption is correct that a CTE will compute a value and materialize it when called, but am concerned it may just abstract the computation being run multiple times.

Sample code to consider:

with cte as (select 'This is a test////////$$$$$$$$' as val),
    cteReplaceDollar as (select replace(val, '$', '') as val from cte),
    cteReplaceSlash as (select replace(val, '/', '') as val from cteReplaceDollar),
    cteReplaceEmpty as (select replace(val, ' ', '') as val from cteReplaceSlash)
select 
    * 
from 
    cteReplaceEmpty
where
    val = 'Thisisatest' and
    val > '' and
    isnumeric(val) = 0;

Is this performing the replace 3 times (an optimistic assumption) or 18 times (a pessimistic assumption)? Basically, does it materialize the intermediate steps similar to variable assignment, or does it work as a generator expression in which each step is performed for every reference of the value and every value in the collection?

Upvotes: 2

Views: 2637

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

Generally, SQL Server doesn't materialise CTE, as opposed to, say, Postgres.

You can confirm it by examining the actual execution plan for your query. I'd recommend SentryOne Plan Explorer, it is a great tool.

https://www.sentryone.com/plan-explorer

I expect to see 7 calls to replace in your example.

Well, I miscalculated. The real answer is:

you should check the actual execution plan.

In your example it looks like this:

Filer

9 calls to replace in Filter operator.

Compute scalar

plus 3 calls in Compute Scalar operator.

12 in total.

So, we confirmed that SQL Server didn't materialise CTE in this example. (It was SQL Server 2017 Developer Edition)

Some further reading:

What's the difference between a CTE and a Temp Table?

Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?

Use of With Clause in SQL Server

There is a suggestion for Microsoft to add a Materialize hint for CTE, similar to what Oracle offers: T-SQL Common Table Expression "Materialize" Option

Upvotes: 4

Related Questions