This blog post explains ISNULL and COALESCE functions in SQL Server in a very simple way with examples. We would also cover some of major differences between these two system functions.
ISNULL()
Syntax : ISNULL(check_ValOrExp, replace_ValOrExp)
COALESCE()
Syntax : COALESCE(check_ValOrExp1, check_ValOrExp2,…, replace_ValOrExp)
COALESCE validates the list of check_ValOrExp and if it finds first non NULL value then it uses as the output else replaces with replace_ValORExp.
COALESCE is equivalent to :
CASE WHEN check_ValOrExp1 IS NOT NULL THEN check_ValOrExp1
WHEN check_ValOrExp2 IS NOT NULL THEN check_ValOrExp2
...
...
ELSE replace_ValOrExp END
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:
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)
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.
Select isnull(Parenttable,'NA'),coalesce(ParentTable,'NA') From TableName
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.
Declare @Param1 varchar(1) = NULL
Select isnull(@Param1 ,'NA') IsnullVal,coalesce(@Param1 ,'NA') CoalesceVal
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.
CREATE TABLE #Demo
(
col1 INTEGER NULL,
col2 AS COALESCE(col1, 0) PRIMARY KEY,
col3 AS ISNULL(col1, 0)
);
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),
col3 AS ISNULL(col1, 0) PRIMARY KEY
);
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!
So, the first thing we need to make sure to get this db out of availability group. To do, we need to remove this database from the availability Group with the below code.
SQLZealots 