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:
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:
|
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_2
…
ELSE 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
|
You are welcome Sam!
ReplyDeleteThank You Sir for Sharing this - It was really helpful! Download CleanMyMac 3 Cracked
ReplyDelete