Using Common Table Expression (CTE) – Did you know…

Today I will write just a short blog post to do a quick reminder!

I still hear a lot of people suggesting CTEs because they think it works like a temporary table (you populate the table and then it can be/is reutilized).

It doesn’t!

From de documentation:

Specifies a temporary named result set, known as a common table expression (CTE).

Maybe they are focusing on the “temporary” word.

Using the CTE two times will perform two different executions! Don’t believe me? See the next example!
If we run the following code do you expect to get the same value for both queries? Note: we have a UNION ALL between them.

WITH cte AS
(
	SELECT NEWID() AS Col1
)
SELECT Col1
  FROM cte
UNION ALL
SELECT Col1
  FROM cte

Sorry to disappoint you but it will run the CTE’s code twice and return the value(s) from each execution.
As we are using the function NEWID(), two different values will be generated.

output.png

To complete the question: “Did you know that CTE’s code will be executed as many times as you use it?”

Thanks for reading!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s