case versus isnull versus coalesce

I am just reading through the documentation for the SQL Server 2012 exams and I saw the following point:


case versus isnull versus coalesce

Now, I know HOW to use each one but I don't know WHEN to use each one. Please could some body clarify. Thanks.

Ps. Please could we have a tag for exam questions?

ISNULL - available only in SQL Server. Allows for the testing and replacement of a NULL value with one another one.

COALESCE - ANSI standard. Allows for the testing and the replacement of a NULL value with the first non-null value in a variable length set of arguments. It is important to note that data type precendence factors into this

-- Conversion failed when converting the varchar value 'a' to data type int
SELECT COALESCE(CAST(NULL AS varchar(10)), 'a', 1) 
-- Returns 1
SELECT COALESCE(CAST(NULL AS varchar(10)), 1, 'a')

In the above example, 'a' is the first non-null value but character data has lower precedence than integer.

Another consideration between ISNULL vs COALESCE is the resultant of ISNULL is determined to be NOT NULL while the outcome of a COALESCE call is NULLable. See JRJ's post ISNULL() <> COALESCE() While that might seem a trivial thing, the query optimizer can make different plans based on the nullability of a column.

You can easily test the nullability of your isnull/coalesce/case expressions by running it through the dmo sys.dm_exec_describe_first_result_set

-- these all evaluate to not nullable (is_nullable = 0) because I'm not clever enough
DECLARE @sql nvarchar(4000) = N'
SELECT ISNULL(NULL, 1) AS I
, COALESCE(NULL, 1) AS C
, CASE WHEN NULL = NULL THEN NULL ELSE 1 END AS C1
'
SELECT
    DMO.*
FROM
    sys.dm_exec_describe_first_result_set(@sql, NULL, 0) AS DMO

CASE - Also an ANSI standard scalar function. I'd look at using CASE over the previous two when I have a test that can't be expressed in a simple scalar but that's a pretty weak answer, I admit.

StackExchange Database Administrators Q#30008, answer score: 11