SELECT COALESCE(@x, @y, @z);
SELECT ISNULL(@x, @y);
When you run this code, both functions return the integer 1759.
One apparent advantage that COALESCE has over ISNULL is that
it supports more than two inputs, whereas ISNULL supports only
two. Another advantage of COALESCE is that it’s a standard function
(namely, defined by the ISO/ANSI SQL standards), whereas ISNULL
is T-SQL–specific. These differences between the two functions are
fairly straightforward.
Next, I discuss some additional differences, some of which are more
subtle. In some respects, ISNULL has advantages over COALESCE.
Data Type of Expression
COALESCE and ISNULL differ in how they determine the data type of
the resulting expression.
The data type of a COALESCE expression is the data type of the
input argument with the highest data type precedence. If all inputs
are the untyped NULL literal, you get an error.
The data type of an ISNULL expression is the data type of the first
input. If the first input is an untyped NULL literal, the data type of the
result is the type of the second input. If both inputs are the untyped
literal, the type of the output is INT.
As an example, consider the following code and its output, which
Table 1 shows.
DECLARE
@x AS VARCHAR( 3) = NULL,
@y AS VARCHAR( 10) = '1234567890';
SELECT
COALESCE(@x, @y) AS COALESCExy, COALESCE(@y, @x)
AS COALESCEyx,
