The NULL Dilemma : The NULL is not equal to NULL : Part-3

In part 1 and part 2 we learned about NULL propagation , ISNULL,COALESCE, tristate logic etc.

Now let’s learn few remaining parts.

SET ANSI_NULLS

As we saw comparing NULL with another NULL returns  UNKNOWN . This is when ANSI_NULLS is ON .

This is ANSI standard to have this value as ON. But in older sql server versions  we can get rows returned when we query as WHERE col=NULL .This is in legacy codes . As Microsoft is deprecating this feature avoid using it.In future versions it will always be ON.

More in

http://msdn.microsoft.com/en-us/library/ms188048%28v=sql.105%29.aspx

NULLIF :

NULLIF function takes 2 arguments and returns null if they are equal , else it returns the first argument.

The 2 arguments should be of same datatype or convertible types.

Eg.

SELECT NULLIF(‘satya’,’saty’)  => ‘satya’

SELECT NULLIF(‘satya’,’satya’)  => NULL

Below is an apt scenario where it can be used

Say we are doing a division operation and want to select NULL if divisior is 0 then we can make use of NULLIF function.

SELECT   col1/NULLIF(col2,0) from table1

Few other facts:

• A primary key column can’t have NULL values.
• A Unique key column can have maximum 1 NULL values(Unless a filtered unique index is used…)

Eg.

 create table #temp  
 (  
 a int unique ,  
 b int  
 )  
 insert into #temp values(1,11)  
 insert into #temp values(2,22)  
 insert into #temp values(NULL,33)  
 insert into #temp values(NULL,44)  

The last insert is not allowed .

How ever below will allow duplicate NULL

 create table #temp  
 (  
 a int,  
 b int  
 )  
 CREATE UNIQUE INDEX ind ON #temp(a)  
 WHERE a IS NOT NULL  
 insert into #temp values(1,11)  
 insert into #temp values(2,22)  
 insert into #temp values(NULL,33)  
 insert into #temp values(NULL,44)  

•  For GROUP BY and DISTINCT,  NULL behave differently .For example for DISTINCT  columns with NULL are not distinct(which should be as 2 NULLs cannot be compared to same)

 create table #temp  
 (  
 a int,  
 b int  
 )  
 insert into #temp values(1,11)  
 insert into #temp values(2,22)  
 insert into #temp values(NULL,33)  
 insert into #temp values(null,44)  
 insert into #temp values(1,55)  
 select distinct(a) from #temp  
 o/p  
 NULL  
 1  
 2  
 select a,SUM(b) from #temp group by a  
 o/p  
 NULL   77  
 1       66  
 2       22  
 drop table #temp