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

ISNULL and COALESCE开发者_运维技巧 though equivalent, can 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.

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

---

It determines the nullability of, say, a computed column using ISNULL or COALESCE

RowCheckSum AS COALESCE(...)

...means that RowCheckSum column definition has NULL keyword, and

RowCheckSum2 AS ISNULL(...)

...has NOT NULL definition.

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

---

Its badly worded; its trying to say that IsNull always evaluates as a default Not Null column; whilst Coalesce can evaluate to a default 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 view the definitions of the columns of the view (in management studio say) the definition on pdTest shows the columns as

DateTime not null, 
DateTime not null. 

On pdtest2 the definition is

DateTime Not Null, 
DateTime Null. 

If you then add a third view; where the isnull statement is evaluated as null

Create view dbo.pdtest3
as

Select  
    ISNULL(GETDATE(), null) as test1,
    coalesce(GETDATE(), getdate(), null) as test2

The definition of the view data is still Not Null, Null.