Saturday, December 20, 2008

What's the difference between ISNULL & COALESCE

ISNULL()

It replace the NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the type is different.

Syntax

ISNULL ( check_expression , replacement_value )

check_expression

The expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

COALESCE()

Returns the first nonnull expression among its arguments.

Syntax

COALESCE ( expression [ ,...n ] )

expression

It'can be of any type.

Note:
ISNULL()
and COALESCE() though both are equivalent, but they behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.

For More Inforamtion :

ISNULL() or COALESCE()?

Performance: ISNULL vs. COALESCE

ISNULL() <> COALESCE(). Discuss

What Is th difference between Is Null & COALESCE

ISNULL (Transact-SQL)

COALESCE (Transact-SQL)

No comments: