Select internals

Naomi 7,361 Reputation points

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
 , '-'
 , 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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,597 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHong-MSFT 10,046 Reputation points

    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,

    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".

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP

    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.

    1 person found this answer helpful.
    0 comments No comments