There is always a discussion going on somewhere over having NULL values in a database. Some people (such as myself) are fine with NULL values. Some people don’t like having NULL values in there database for some reason. I’ve heard all sorts of reasons; “The screw up performance”, “They are hard to deal with”, “I like using a default value better”, “Default values are easier to work with”.
The performance one is just bunk. NULL values are indexed just like any other value.
The rest of the reasons to not use NULL values ignore the fact that a NULL value is a perfectly legit value for some fields. Not all fields should allow a NULL value to be placed in them. If you are building a shopping cart and have a line items table, the Quantity and Price fields probably shouldn’t allow nulls as you should know the price and quantity when creating the invoice. However in the same shopping card application the users phone number may need to allow NULL values.
Something which is key to remember about NULL values is that NULL doesn’t mean nothing. It means unknown. There may be a value, there may not, we simply do not know what that value is. Putting in some default value will then put a value on the field when there shouldn’t be. A great example which I read recently involved a hypothetical database at a blood bank. A person comes in needing blood. They are entered into the system but the recipients blood type isn’t known. If you put in a default value of a blank value when you go to query the supply table, if there are pints of blood which have not been typed they will also have a blank value (as that is your standard default value). You’ll get a match when you run the query and possibly be giving the recipient blood which is of the wrong type. Now if you were using NULL values in the database the query would return no matching values as NULL <> NULL (under the default settings of Microsoft SQL Server). Granted we should be able to trust that the person give the recipient the blood would check the typing, but would you rather trust a person or the computer. People are overworked and usually underpaid, and nurses and doctors make simple mistakes just like the rest of us. Personally I’d rather know that the system wouldn’t find a match rather than have to worry that I’d be getting the wrong type of blood.
If you really want to display that default value when the data is returned that’s what the ISNULL system function is for. This way you don’t have to write long case statements around each field which allows NULL.
NULL values have there place, just like data does. NULL values don’t mean that we don’t care about the data, it means that we simply don’t know the value of the data as of yet. It shows that we care about the system and the people using it. How annoying is it to look at a form which is full of default values and having to read through all of them instead of simply looking at the ones which are blank.