CTE with function columns

Question

CTE with function columns

richarddelcarmen

SSChasing Mays

Points: 625
More actions
February 21, 2011 at 2:16 am

#235284

Good day guys,
I have a question about Common Table Expressions. I know that the query inside the CTE is always called each time the CTE is referenced. However, am I right to assume that the expressions or functions inside the CTE will only be called once after the CTE has been referenced?
Confusing isn't it? Here's my question in example:
WITH test AS
(
SELECT DATEPART(WK, column1) AS week, column2, YEAR(column3) AS year
FROM table1
)
SELECT a.week, SUM(a.column2)
FROM test a
LEFT OUTER JOIN table3 b ON a.year = b.yearontable3
WHERE a.week > 10
GROUP BY a.week
The CTE named "test" in the SELECT above is only referenced once which means that the query inside the CTE will be executed only once. But the column week is used many times. Does the function inside the column called once or every time the column is used?
This is the version of the query without CTE:
SELECT DATEPART(WK, a.column1), SUM(a.column2)
FROM table1 a
LEFT OUTER JOIN table3 b ON YEAR(a.year) = b.yearontable3
WHERE DATEPART(WK, a.column1) > 10
GROUP BY DATEPART(WK, a.column1)
Thanks very much.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

The CTE is just an alternative syntax, it is not "called" or "executed": it is used in the statement as if it was a view.

The optimizer expands the CTE and accesses the tables directly, it does not materialize the CTE in any way.

As for the function call, try looking at the execution plan. You will see that the DATEPART function is applied just once to "column1" (you should see a compute scalar operator). The output of that operator is an expression that is used in the select list, the filter predicate and the GROUP BY clause.

Hope this helps

Gianluca

-- Gianluca Sartori

richarddelcarmen SSChasing Mays Points: 625 More actions · Answer 2

Sorry for the late reply.

Knowing that the query optimizer will only compute the scalar function once and then use the return value for the rest of the SELECT statement makes me feel at ease.

Thanks "Gianluca Sartori".

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Hi Richard, some days ago I read this interesting blog post on this exact subject.

http://sqlblog.com/blogs/paul_white/archive/2011/02/27/bug-slow-sums-and-averages.aspx

Hope you find it useful.

Gianluca

-- Gianluca Sartori

richarddelcarmen SSChasing Mays Points: 625 More actions · Answer 4

Thanks again Gianluca.

I already bookmarked the page since it contains two more interesting articles about SQL Index and T-SQL performance tuning. I will read the topic about the aggregates.

Thanks for the link, it's really fun to learn about the inner workings of a technology.

Good day.

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Paul White's blog is probably the best online resource for SQL Server internals.

Good day to you too.

-- Gianluca Sartori