CODEWITHSUNDEEP
sqlsql-serverperformanceuser-defined-functions
vdh_ant
vdh_ant

Reputation: 13186

Calling a function from within a select statement - SQL

I have the following statement:

SELECT CASE WHEN (1 = 1) THEN 10 ELSE dbo.at_Test_Function(5) END AS Result

I just want to confirm that in this case the function wont be executed?

My reason for asking is that the function is particularly slow and if the critiria is true I want to avoid calling the function...

Cheers Anthony

Upvotes: 1

Views: 11748

Answers (4)

Remus Rusanu
Remus Rusanu

Reputation: 294497

Do not make this assumption, it is WRONG. The Query Optimizer is completely free to choose the evaluation order it pleases and SQL as a language does NOT offer operator short-circuit. Even if you may find in testing that the function is never evaluated, in production you may hit every now and then conditions that cause the server to choose a different execution plan and first evaluate the function, then the rest of the expression. A typical example would be when the server notices that the function return is deterministic and not depending on the row data, in which case it would first evaluate the function to get the value, and after that start scanning the table and evaluate the WHERE inclusion criteria using the function value determined beforehand.

Upvotes: 3

Aaron Alton
Aaron Alton

Reputation: 23236

Your assumpion is correct - it won't be executed. I understand your concern, but the CASE construct is "smart" in that way - it doesn't evaluate any conditions after the first valid condition. Here's an example to prove it. If both branches of this case statement were to execute, you would get a "divide by zero" error:

SELECT  CASE
                WHEN 1=1 THEN 1
                WHEN 2=2 THEN 1/0
            END AS ProofOfConcept

Does this make sense?

Upvotes: 4

jvanderh
jvanderh

Reputation: 2955

Put a WaitFor Delay '00:00:05' in the function. If the statement returns immediately it didn't execute if it takes 5 seconds to return then it was executed.

Upvotes: 0

Gary.Ray
Gary.Ray

Reputation: 6501

Assuming you are doing some kind of testing... If you are trying to avoid the at_Test_Function, why not just comment it out and do

SELECT 10 AS Result

Upvotes: 0

Related Questions