Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
COALESCE is expanded out to CASE but obviously in a CASE statement you write yourself you can be more flexible in the WHEN conditions. For ISNULL vs COALESCE related/duplicate? –  Martin Smith Dec 7 '12 at 23:43
    
related I agree. but not dup. Thanks for the comment :) –  Stuart Blackler Dec 8 '12 at 0:00
add comment

2 Answers

up vote 8 down vote accepted

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.

share|improve this answer
    
Thanks for the complete answer so quickly. I had no idea that the order of precedence mattered for COALESCE. Thanks again –  Stuart Blackler Dec 8 '12 at 0:11
    
CASE is technically an expression, not a function. –  ypercube Dec 8 '12 at 0:59
add comment

ISNULL gives you the possibiltity to return 1 repalcement, COALESCE is not limited to one Value e.g. COALESCE(v1,v2,v3,v4,v5) if V5 is the only Value which is NOT NULL it will be returned

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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