That means, this is how SQL Server is resolving internally while performing ISNULL function. To me, COALESCE is a beautified format of CASE WHEN…ELSE END. Differences between ISNULL & COALESCE 1. Query execution & evaluation method ISNULL function evaluates the check_ValOrExp as below:
CASE WHEN check_ValOrExp1 IS NOT NULL THEN check_ValOrExp1 WHEN check_ValOrExp2 IS NOT NULL THEN check_ValOrExp2 ... ... ELSE replace_ValOrExp END
COALESCE evaluates the check_ValOrExp as below: Its similar to ISNULL, however, it does the evaluation until it finds first NON NULL value from Left to right defined fields in COALESCE. It is very important to be noted that, If check_ValOrExp is another query then it has to execute twice; one for checking the expression and the other for assignment part. Let us look at this behavior from execution plan from a very simple query for everyone to understand better.
If check_ValOrExp is NOT NULL it returns check_ValOrExp (Return type is the same as type of check_ValOrExp) If check_ValOrExp is NULL then If replace_ValOrExp is NOT NULL it returns replace_ValOrExp (Return type is the same as type of check_ValOrExp) Else if replace_ValORExp is null then it returns NULL (Return type is int)
Execution Plan (XML format) snapshot: So, depending on your isolation level, you might end up with unexpected results. So, I personally tried to avoid COALESCE for validating any subquery/corelated query outputs. Otherwise, we might need to handle it differently to run the sub query only once. In one sentence – ISNULL is a function but COALESCE is an Expression in SQL Server. 2. Return Datatype ISNULL function returns the datatype of the first parameter. COALESCE returns the data type of value with the highest precedence.
Select isnull(Parenttable,'NA'),coalesce(ParentTable,'NA') From TableName
3. Input Parameters ISNULL function needs ONCE and ONLY two parameters. Whereas COALESCE can have variable number of parameters. (Technically, I would not call this as input parameters since COALESCE is not a function.) 4. NULLability of result The ISNULL return value is always considered NOT NULLable. By contrast,COALESCE with non-null parameters is considered to be NULL. Micorosoft has provided a very good example, nothing to beat with a better one as below. This statement fails because the PRIMARY KEY cannot accept NULL values and the nullability of the COALESCE expression for col2 evaluates to NULL.
Declare @Param1 varchar(1) = NULL Select isnull(@Param1 ,'NA') IsnullVal,coalesce(@Param1 ,'NA') CoalesceVal
This statement succeeds because the nullability of the ISNULL function evaluates AS NOT NULL.
CREATE TABLE #Demo ( col1 INTEGER NULL, col2 AS COALESCE(col1, 0) PRIMARY KEY, col3 AS ISNULL(col1, 0) );
Hope you enjoyed this post, share your feedback in comment section. Recommending to go through “Microsoft SQL Server – Beginners Guide” series for more information. I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
CREATE TABLE #Demo ( col1 INTEGER NULL, col2 AS COALESCE(col1, 0), col3 AS ISNULL(col1, 0) PRIMARY KEY );