Have Geek Brain

Calling a function from 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 the function will fail in this case?

My reason is that the function is especially slow, and if the criticism is correct, I want to avoid calling the function ...

Cheers Anthony

0


a source to share


4 answers


Don't make this assumption, it is WRONG . The query optimizer is completely free to choose the order of evaluation it likes, and SQL as a language does NOT offer operator short-circuiting. Even though you may find in testing that a function is never evaluated, during production you may occasionally run into conditions that cause the server to choose a different execution plan and evaluate the function first and then the rest of the expression. A typical example would be when the server notices that the function is returning a deterministic and data-independent row, in which case it first evaluates the function to get the value, and then starts a table scan and evaluates the WHERE criteria using the function's predefined value.



+3


a source


Your assumption is correct - it will not be fulfilled. I understand your concern, but the CASE construct is smart this way - it does not evaluate any conditions after the first valid condition. Here's an example to prove it. If both branches of this case statement were to be executed, you will receive a divide by zero error:

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


It makes sense?

+4


a source


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

SELECT 10 AS Result
0


a source


Place a WaitFor Delay '00:00:05'

in a function. If the statement returns immediately, it was not executed, if it takes 5 seconds to return, then it was executed.

0


a source






More articles:


All Articles