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
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