22

The docs insist that ISNULL is a function, but COALESCE is not. Specifically, they say

The ISNULL function and the COALESCE expression

If I put on my Lisp hat, I can think of COALESCE as a macro and everything makes sense. However, I've never needed such thinking in T-SQL. So, I must ask. In T-SQL:

  1. What is the definition of a function?
  2. What is the definition of an expression?
  3. How can I tell the difference between a function and an expression?
0

2 Answers 2

51

It's generally a mistake to look for consistency in SQL Server, even (or especially) in the documentation.

You have to remember SQL Server is a mature product that has been developed (and documented) by many different people and teams over the years. It is natural to look for definitions and standardisation, but that isn't always an attainable goal.

As far as it really matters, one might define a function as a single entity that takes a variable number of parameters (including none) and returns a value (scalar or set returning).

One might define an expression as any sequence of symbols and operators that T-SQL can evaluate to produce a result. A function would be a proper subset of all expressions in that case. I don't know how useful that is to anyone on a practical basis.

One might also define a SQL Server function as something documented to be function, and anything else as an expression. Not very intellectually satisfying.

One might also argue that COALESCE isn't a function* because there is no built-in function by that name (it expands to a CASE expression), which is a little circular.

Then again, the documentation refers to CHOOSE and IIF as logical functions but they also expand to CASE expressions. Entertainingly, the internal function that implements CASE is ScaOp_IIF so 🤷‍♂️.

You can find any number of these inconsistencies. In that sense, there are no simple (yet comprehensive) answers to your questions.

You might ask why the SQL Server documentation makes an exception for COALESCE. My feeling is it was done to emphasise the expansion of COALESCE, which can lead to unexpected results due to multiple evaluations:

-- Often returns NULL
SELECT 
    COALESCE
    (
        (SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 
        999
    );

The above COALESCE expands to:

CASE 
    WHEN 
    (
        CASE 
            WHEN rand()<=(5e-001) THEN (999) 
            ELSE NULL 
        END 
    )
    IS NOT NULL THEN 
    (
        CASE 
            WHEN rand()<=(5e-001) THEN (999) 
            ELSE NULL 
        END
    )
    ELSE (999) 
END

Related Q & A: Does SQL Server read all of a COALESCE function even if the first argument is not NULL?


* If it makes you feel any better, Oracle and PostgreSQL call COALESCE a function.

0
17

According to the SQL standard (ISO/IEC 9075-2:2023(E)), case is an expression, and coalesce is a case abbreviation.

<case expression> ::=
    <case abbreviation>
  | <case specification>

<case abbreviation> ::=
    NULLIF <left paren> <value expression> <comma> <value expression> <right paren>
  | COALESCE <left paren> <value expression> { <comma> <value expression> }... <right paren>
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.