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".
|
Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..
|