Null Functions

ISNULL : It validates the expression and replaces to a specified value if it is null, other wise it will returns the expression.

ISNULL(expression,specifiedvalue)

NULLIF : It compaares the expressions and returns NULL if both the expressions are same, otherwise it will returns first explression.

NULLIF(expression1,expression2)

COALESCE : It will return the first Non Null expression from the specified expressions, if all expressions are NULL then it will returns NULL.

COALESCE(expression1,expression2,expression3.....,expressionN)


GETANSINULL : It returns the default nullability of the specified database.

GETANSINULL('databasename')

When the nullability of the specified database allows for null values and the column or data type nullability is not explicitly defined, GETANSINULL returns 1. This is the ANSI NULL default.

Reference : GETANSINULL


check this code

SET ANSI_NULL_DFLT_ON off
select getansinull('master')
create table m(id int,val char)
insert m values(2,null)

This query returns the below error,

Cannot insert the value NULL into column 'val', table 'master.dbo.m'; column does not allow nulls. INSERT fails.
The statement has been terminated.


SET ANSI_NULL_DFLT_ON on
select getansinull('master')
create table p(id int,val char)
insert p values(2,null)

This query working fine.

Note : Twist in ISNULL Function