T-SQL: What does this passage mean in the COALESCE documentation?

Question

ISNULL and COALESCE, although equivalent, can behave differently. An expression including ISNULL with non-zero parameters is considered NOT NULL, and expressions with COALESCE with non-zero parameters are considered NULL.

http://msdn.microsoft.com/en-us/library/ms190349.aspx

Answer 1

Its poorly worded; his attempt to say that IsNull always evaluates as the default Not Null column; while Coalesce can evaluate the default value of Null.

Create view dbo.pdtest as Select ISNULL(GETDATE(), GETDATE()) as test1, coalesce(GETDATE(), getdate()) as test2 go Create view dbo.pdtest2 as Select ISNULL(GETDATE(), GETDATE()) as test1, coalesce(GETDATE(), getdate(), null) as test2 

When you look at the definitions of presentation columns (for example, in a management studio), the definition on pdTest shows the columns as

 DateTime not null, DateTime not null. 

In pdtest2 definition

 DateTime Not Null, DateTime Null. 

If you then add a third view; where the expression isnull evaluates to null

 Create view dbo.pdtest3 as Select ISNULL(GETDATE(), null) as test1, coalesce(GETDATE(), getdate(), null) as test2 

Defining view data is still not Null, Null.

Answer 2

It determines the invalidity of, for example, a computed column using ISNULL or COALESCE

 RowCheckSum AS COALESCE(...) 

... means the RowCheckSum column RowCheckSum has the NULL keyword and

 RowCheckSum2 AS ISNULL(...) 

... has a definition of NOT NULL .

It also means that in the result set, the first field can return NULL values, and the second cannot.