Are the columns returned from CROSS APPLY with a Table Valued Function evaluated only once?

Question

Considering the following query:

select Student.Id,
       case when SomeTvfResult.Num = 3 then N'foo'
            when SomeTvfResult.Num > 42 then N'bar'
            else N'baz'
       end as SomeStr
from Student
cross apply SomeTableValuedFunction(Student.Id) SomeTvfResult

I wonder if the value of SomeTvfResult.Num gets recalculated each time I reference it in the case statement or it's somehow cached and evaluated only once. If it's relevant, you can assume that SomeTableValuedFunction is an inline TVF.

Since I've learned that coalesce is simply replaced by a case statement that causes the given expressions evaluated more than once I feel concerned each time I use some function in my queries.

Answer 1

The way you have phrased the question indicates you fear that the whole TVF will be evaluated multiple times. This is certainly not the case, as with an inline TVF the inner query is effectively pasted into the outer query as a derived table.

What you can possibly be concerned about is whether the computation of a computed column in the TVF is deferred or not. Unfortunately, the XML query plan has no info on this (you would be looking at the relevant COMPUTE SCALAR).

Paul White has a very good blog post explaining this. Often SQL Server will defer the calculation of a Compute Scalar to later in the plan, and this can become an issue with very complex or computationally difficult calculations.

For example, PATINDEX and SUBSTRING (and even REVERSE) are often used in tandem to do string manipulation. A common trick to avoid lots of copy-pasta is to CROSS APPLY (VALUES) of one calculation, then reference it in another CROSS APPLY, similar to how you might do it procedurally.

What I have noticed from query plans is that the optimizer collapses the whole thing together, no nested loops, no constant scans, which is normally the right idea. As I understand it, the execution engine will afterwards pull out various pieces which it thinks need pre-calculating and storing, and some which it will calculate when needed, but sometimes it can get it wrong, especially when dealing with very large strings.

What you can do in such a case instead is to use OUTER APPLY (VALUES), this appears to create an optimization fence, causing a nested loop with a constant scan. Do not do this blindly. Test it, time it, look at CPU time etc.