A NULL indicates that a value for an entry is not available. This makes a null different from a zero or an empty or blank value. You'll find NULLs valuable when you need to know if a value is either unknown or missing. Many database designers (but not all) eschew the use of NULLs and recommend that you use default values in your tables. It's common practice to substitute the character string "NA" or "N/A" when a value is not available.
NULLS have implications when it comes to grouping, sorting and in particular statistical analysis; but NULLS can still be very useful. One place you can't have NULLS are in primary keys (each value must be unique), or where an identity property for a column must contain a valid value.
SQL Server inserts a NULL into any column in a new row where you don't set the value for that column, and where a default value isn't assigned. For any column with a default value, a NULL is replaced by that default value. You can also set the default value to NULL by using the NULL keyword.
You can set default values in the Enterprise Manager's New Table and Design Table views; and there you can allow or disallow NULLS. If you set a default value for a column, then that default value is always used when there isn't a value specified for entry. Thus, if you allow NULLs, then you will only see NULLs when there is no entry and no default value set, or when you specifically enter the NULL value. For a column where NULLs are disallowed, no data entry or entering a NULL value will create an error condition.
If you use a NULL as part of a query, how NULL values are handled is a function of the SQL-92 ANSI standard. Any NULLs used in a comparison such as =, <, or > will evaluate to FALSE. If the ANSI NULLs option is OFF, then SQL Server will make this comparison, and if a value in both columns is NULL, then the comparison will evaluate to TRUE.
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics.
