top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What is COAlESCE in SQL Server and how it is different from ISNULL function??

+2 votes
5,038 views
What is COAlESCE in SQL Server and how it is different from ISNULL function??
posted Oct 7, 2013 by Atul Mishra

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

0 votes

ISNULL is function and the COALESCE is an expression both are used for similar purpose but can behave differently.

  1. Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.
  2. Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.
  3. The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. 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, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example.
answer Oct 18, 2013 by Salil Agrawal
0 votes

The main difference in both is ISNULL replace value of any column if it is NULL to something else

Select ISNULL(NULL,5) Result- 5
While COALESCE gives value of first not null coulmn

Select COALESCE (NULL,NULL,5,NULL) Result- 5

But Mainly we use coalesce where we want to get value of a column as a comma seperated values-

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product
SELECT @listStr

answer Nov 16, 2013 by Neeraj Pandey
...