SELECT
COLUMNPROPERTY(OBJECT_ID('dbo.T1'), 'COALESCE_ 1_ 2',
'AllowsNull') AS COALESCE_ 1_ 2,
COLUMNPROPERTY(OBJECT_ID('dbo.T1'), 'COALESCE_col1_0',
'AllowsNull') AS COALESCE_col1_0,
COLUMNPROPERTY(OBJECT_ID('dbo.T1'), 'ISNULL_col1_0',
'AllowsNull') AS ISNULL_col1_0;
You get the output shown in Table 2.
Table 2: Nullability of Output
COALESCE_ 1_ 2 COALESCE_col1_0 ISNULL_col1_0
------------ --------------- -------------
0 1 0
The expression COALESCE( 1, 2) resulted in a nonnullable column because both inputs were nonnullable. The expression
COALESCE(col1, 0) resulted in a nullable column because one of
the expressions was nullable (col1). The expression ISNULL(col1, 0)
resulted in a nonnullable column because one of the expressions was
nonnullable (0).
When you’re done, run the following code for cleanup:
DROP TABLE dbo.T0, dbo.T1;
Used with Subqueries
The ISNULL function has an important advantage over COALESCE
in that internally it doesn’t evaluate an input expression more than
once. In accordance with standard SQL, COALESCE(v1, v2) is simply
internally translated to CASE WHEN v1 IS NOT NULL THEN v1 ELSE
v2 END. As a result, SQL Server might evaluate the expression v1
more than once, which can lead to all kinds of surprising results. This
