isnull vs coalesce sql server

The ISNULL() function is used to replace NULL with the specified replacement value. This function contains only two arguments.

The ISNULL() can only have one input. So, ISNULL() is faster than COALESCE().

The ISNULL() uses the first parameter type.

The ISNULL() function is a T-SQL.

Syntax: SELECT ISNULL (NULL, 1)

The COALESCE() can have multiple inputs and it will evaluate in order until one of them is not null.

The COALESCE() function returns the first non-null value of its arguments.

The COALESCE() not limited to arguments but must be of the same data type.

The COALESCE() follows the CASE expression and returns the first non-null value.

The COALESCE() is ANSI-Standard.

Syntax: SELECT COALESCE(NULL, NULL, 1, NULL)

Summary:

The NULL value for ISNULL() is converted to INT whereas for COAELSCE() you have to provide a type.

a) ISNULL(NULL, NULL) — Ruturns as int

b) COALESCE(NULL, NULL) — Ruturns an error.

c) COALESCE(CAST(NULL as int), NULL) — Ruturns as int after cost.

The ISNULL() is a T-SQL function but The COALESCE() is ANSI-Standard.

--Example 1

DECLARE @Temp VARCHAR(3)
    SET @Temp = NULL;
SELECT ISNULL(@Temp, 'TEST') AS 'RESULT ISNULL'
SELECT COALESCE(@Temp, 'TEST') AS 'RESULT COALESCE'
-- ISNULL limited to @Temp length but COALESCE() not limited to the @Temp length
--In the above, the temp variable has length 3.So the ISNULL function returns tes and the COALESCE() function does not; depending on the length, it returns test.

--Example 2

DECLARE @Temp1 VARCHAR(4)='TEST'
DECLARE @Temp2 INT = 5
SELECT ISNULL(@Temp1, @Temp1) AS 'RESULT ISNULL'
-- Success

GO
SELECT COALESCE(@Temp1, @Temp1) AS 'RESULT COALESCE'
-- Error : Must declare the scalar variable "@Temp1".

Pin it

Hi there! Welcome to my blog. My name is Anil Singh. I'm a author of this blog. For more detail about me, kindly refer to this link..
My Blogs - http://code-sample.com and http://code-sample.xyz
My Books - Interview Questions and Answers Books- Get Your Book in 15+ Digital Stores Worldwide..

                          You Might Also Like                
                         