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

Expert database 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 usage:

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.

Related articles:

Strategies for approaching NULL values with SQL Server

Using Nulls in SQL Server