FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.T1')
AND name = N'col1';
You get 'int' as output.
When you’re done, run the following code for cleanup:
DROP TABLE dbo.T1;
On the surface, it
might seem that
there are only a
couple of minor
differences
between the
COALESCE and
ISNULL functions.
Nullability of Expression
If you need to store the result of a COALESCE or ISNULL expression
in a table, you probably care whether the resulting column is nullable
or not. The way nullability of the expression is determined is different
for the two functions. To demonstrate the difference, I’ll use a table
called T0, which the following code creates and populates:
SELECT CAST(NULL AS INT) AS col1 INTO dbo.T0;
With COALESCE, the resulting column is defined as NOT NULL only
if all expressions are nonnullable and NULL otherwise. With ISNULL,
the resulting column is defined as NOT NULL if any expression is
nonnullable and NULL if both are nullable. As an example, run:
SELECT
COALESCE( 1, 2) AS COALESCE_ 1_ 2,
COALESCE(col1, 0) AS COALESCE_col1_0,
ISNULL(col1, 0) AS ISNULL_col1_0
INTO dbo.T1
FROM dbo.T0;
Then, check the nullability of the resulting columns by running the
following query:
www.sqlmag.com
sql server Pro / may 2013
