Select internals

Question

Hi everybody,

This is probably a simple question, but I want to know for sure.

Say, I have a query from 4 temp tables which are all in the UNION and the select concatenates all columns into one separated string (it is done in SQL). My question is - if I have several columns repeated and wrapped into several functions including inline scalar UDF - when the query executes, are these columns being sent to these custom scalar functions multiple times for the same number they are used in the string? If yes, would it make sense to somehow re-write this query, to, say, use cte to pre-calculate (pre-format) these columns to re-use?

In other words, here is a sample:

+ SUBSTRING(LTRIM(RTRIM(ISNULL(dbo.RemoveSpecialChars(STATE_CD), ''))), 1, 2) + '~' --as STATE_CD
 + LTRIM(RTRIM(dbo.RemoveSpecialChars(ZIP_CD))) + '~' --as ZIP_CD
 + CONCAT(
 UPPER(dbo.RemoveSpecialChars(LTRIM(RTRIM(STATE_CD))))
 , '-'
 , LEFT(dbo.RemoveSpecialChars(LTRIM(RTRIM(ZIP_CD))), 5)
 )

Would it make sense to somehow re-write the above, so that UDF is not called twice on the same column?

I've done some quick tests and CTE way seems to be a winner in SQL 2016 (production).

Thanks in advance.

Answer 1

Hi @Naomi
The SQL Server query optimizer can combine repeated calculated values into a single Compute Scalar operator. Whether or not it will do this depends on query plan costing and the properties of the calculated value. As expected, it will not do this for calculated values which are nondeterministic, which a few exceptions such as RAND(). It also will not do this for user defined functions.

Please refer to this thread for more details: Does SQL Server cache calculated values in a query?

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

You can inspect the query plan to find out. But from my experience, there will be a call for each occurrence of the function. That is, the result is not cached and reused.

Keep in mind that on SQL 2019, the UDF maybe inlined and thus not called as such.