9.11.2012

ISNULL vs COALESCE Functions

Differences Between ISNULL and COALESCE Functions in SQL Server


S.No
ISNULL
COALESCE
1
Meaning:
This function works like “if” condition which we use in other program languages. It takes only two arguments, if first argument is Null then it returns second and one more thing is that the second argument should be of same size that means if first argument has varchar(2) and second has varchar(4) then it truncates last characters.
Meaning:
This function works same as “if else” statement and it takes multiple arguments and it checks Null values for each if first argument is null then it considers the second value, if the both first and second arguments are nulls then it considers third argument.
2
Syntax:
ISNULL(argument1, argument2)
Syntax:
COALESCE(argument1, argument2, argument3[, argument4, argument6, argument7…])
3
Creating Computed Columns:
With ISNULL() we can create both persisted & non-persisted computed columns.
Creating Computed Columns:
With COALESCE() we can not create non-persisted computed column, but only persisted column.
4
Number of Parameters:
Takes only 2 parameters.

Note: If we use more than two parameters with the ISNULL function then we must use nested ISNULL functions.
Number of Parameters:
Takes a variable number of parameters.
5
ANSI standard or not:
A proprietary T-SQL function.
ANSI standard or not:
ANSI SQL standard.
6
Returned Data Type:
Data type returned is the data type of the first parameter.

Returned Data Type:
Data type returned is the expression with the highest data type precedence. If all expressions are non-nullable, the result is typed as non-nullable.
7
Whether translating to CASE Expression possible ?
No
Whether translating to CASE Expression possible ?
Can be translated to a CASE expression:
For Example,
COALESCE (exp_1, exp_2, … exp_n)
Can be translated to
CASE
WHEN exp_1 IS NOT NULL THEN exp_1
WHEN exp_2 IS NOT NULL THEN exp_2ELSE exp_n
END
8
What happens when parameters datatypes are not determined ?
If the data types of both parameters are not determined, the data type returned is int.
ISNULL(NULL, NULL) – Returns int
What happens when parameters datatypes are not determined ?
At least one of the NULL values must be a typed NULL. If the data types of all parameters are not determined, the COALESCE function will throw an error:
COALESCE(NULL, NULL) – Throws an error
COALESCE(CAST(NULL AS INT), NULL) – Returns int
9
Truncating parameter value:
The ISNULL() function looks at the first value and the second parameter value is automatically limited to that length .

Example:
declare @test varchar(3)
select isnull(@test, 'ABCD') AS ISNULLResult

Output:
ISNULLResult
ABC
Truncating parameter value:
COALESCE() does not have this restriction.

Example:
declare @test varchar(3)
select coalesce(@test, 'ABCD') AS coalesceResult

Output:
coalesceResult
ABCD

References:



3 comments: