Jonathan Allen
Jonathan Allen

Reputation: 70337

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

Upvotes: 4

Views: 473

Answers (2)

zerkms
zerkms

Reputation: 255145

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.

Upvotes: 5

u07ch
u07ch

Reputation: 13702

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.

Upvotes: 3

Related Questions