Systems Engineering and RDBMS

Archive for April 12th, 2007

NULL checks

Posted by decipherinfosys on April 12, 2007

Another one of those small mistakes.  A NULL != NULL in SQL language.  One needs to use the IS NULL and/or the IS NOT NULL checks in order to check for NULL values.  And in the SQL Server world, do not change the behavior using ANSI_NULLS setting.  It is un-desirable and leads to confusion.  Here is what I mean by that (SQL Server Syntax):

create table demo_null (col1 int null)
insert into demo_null values (1)
insert into demo_null values (null)
insert into demo_null values (null)
go
set ansi_nulls off
–show 2 records
select * from demo_null where col1 = null
go
set ansi_nulls on
–will show 0 records
select * from demo_null where col1 = null
go

So,  use IS NULL checks always.  Also, remember that whenever the IS NULL or the IS NOT NULL checks are used, if there is an index on that column, the index will not be used since NULL values do not get stored in an index and that is why the optimizer will need to go to the data pages in order to retrieve more information to suffice the requirements of the query.

Posted in DB2 LUW, Oracle, SQL Server | 1 Comment »