question

lilima avatar image
lilima asked

differences between functions

What is the difference between ISNULL() and COALESCE() Thanks in advance.
sql-server-2008functionsnullisnull
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
robbin avatar image
robbin answered
They are both to check whether the field/expression is a NULL value. The basic diffeence is that with ISNULL() you can only specify one replacement value but with COALESCE() you can specify many. COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine. The other differences are 1. Data type determination of the resulting expression - ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence 2. The NULLability of result expression is different for ISNULL and COALESCE. ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE is not. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different NULLability values. This makes a difference if you are using these expressions in computed columns and creating key constraints or making return value of a scalar UDF deterministic so that it can be indexed. Please note that I am referring to expressions that will always return a non-NULLable value here. Otherwise, you can have ISNULL or COALESCE return NULL value just fine. 3. Validations for ISNULL and COALESCE is also different. For example, NULL value for ISNULL is converted to int whereas for COAELSCE you have to provide a type. Ex: ISNULL(NULL, NULL) -- is int COALESCE(NULL, NULL) -- Will throw an error COALESCE(CAST(NULL as int), NULL) -- it valid and returns int 4. ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters 5. COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL function 6. You could get different plans for queries using ISNULL & COALESCE if the expressions involve scalar sub-queries. This will make a performance difference and queries with COALESCE often perform worse For more help you should visit BOL.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Terrific answer (+1), but one of my favorite uses for COALESCE is to string together text from different rows into one varible or column. Of course, you can't do that one with ISNULL().
0 Likes 0 ·
@Mark Thanks a lot for the compliment. But I did not understand your statement for ISNULL. If I understand correctly, were you talking about this? DECLARE @varchar VARCHAR(10) SELECT @varchar = ISNULL(@varchar + ',','') + charvalues FROM ( SELECT 'A' AS charvalues UNION ALL SELECT 'B' )A SELECT @varchar I guess it is possible. OR if you are talking about something else then please share.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.