Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 213

In fact a colleague believes that during the normalization process all columns should start out with not
allowing null. One reason that nullable columns are not recommended is that they can be confusing. The
definition of NULL is that the value for a data item is unknown. Thus, if a table contains null columns,
it could mean that the value for the column is not known at the moment, but it could also mean that
the column doesn't apply to the row. Null columns can also be misleading in aggregate functions. For
example, suppose you included COUNT(columnname) in a query, and that the columnname is nullable.
The result would be a count for all the rows where the column value was null.

Furthermore, there's certainly additional complexity with handling nulls. For example suppose you use
this query:

SELECT *

FROM tableName

WHERE columnA = columnB

Further, suppose that both columnA and columnB are both NULL. This query will not yield any results
because when comparing two nulls SQL Server evaluates that to false. One way to change this behav-
ior is by using the SET ANSI_NULLS option. If the SELECT statement above were preceded with SET
ANSI_NULLS OFF, then the query would yield results. Other useful functions for handling NULLS are
ISNULL and COALESCE.

ISNULL takes two arguments, check_value and replacement_value. In the previous example, columnA
could be replaced with ISNULL(columnA, 'None'). This means that if the value of columnA is not null
then it is returned. If it is null then None is returned. Note also that the data type of ISNULL will match
the data type of columnA.

COALESCE takes n number of arguments. It returns the first non-null value in the list of arguments.
For example, COALESCE(columnA, columnB, columnC, columnD) will return the first value from these
columns which is not null. If they're all null then the return value is also null.

Default Values
Columns will either have a valid value or NULL. However, if you set a column's nullability as not
allowed then you may have a situation where the column's value isn't supplied when a row is created.
The solution to that is to define a default value for that column. The default value is defined when the
table is created. This means that whenever that column is updated, anytime the new value is NULL it will
be replaced by the default definition. For example, suppose you have an Amount column in a table. You
may define the column to have a default value of 0. This way any attempt to put a NULL in the Amount
column will instead put in a 0. Also, the default value doesn't have to be defined with a static value. A
scalar function can also be used as a default. Consider the following table definition:

CREATE TABLE Billing
(BillingID int identitify(1, 1) NOT NULL,
BillTS smalldatetime NOT NULL,
BillAmount smallmoney NOT NULL,

.
.
.
InsertUID varchar(30) NOT NULL DEFAULT suser_sname(),
InsertTS smalldatetime NOT NULL DEFAULT getutcdate(),
UpdateUID varchar(30) NULL,
UpdateTS smalldatetime NULL,