The following is a very difficult topic with no optimal solution within the framework of current RDBMS architecture.
Beginner Microsoft SQL Server T-SQL
developers prefer NULLs, because it is the low maintenance overhead
designers caution against using NULLABLE columns because they
introduce 3-way logic into joins, and forces the frequent use
of ISNULL function. You cannot use for example:
WHERE OrderDate = NULL
You have to use:
WHERE OrderDate is NULL
To avoid NULL
1. Set default
on each column such as 0, '' (empty string), '01/01/1900' (SQL Server default date 1900-01-01).
2. If you have
to know that column was populated or not, add a IsColumnX bit
column with default 0. Update it to 1 when ColumnX gets populated. IsColumX can be used in SQL queries as a regular column without 3-way logic.
Strategies for approaching NULL values with SQL Server
Using Nulls in SQL Server