NULL vs Default Value in SQL Server Database

Question

Are there any implications to using default values, such as empty strings, when inserting rows into a database. The problem with using nulls that they have to be checked for when using the data in an application, whereas a default value can be handled much easier.

Answer 1

The meaning of NULL in a database should be reserved for 'unknown' which is not the same as empty. So as long as the default values you use reflect the nature of the underlying data, (i.e. it is not 'unknown') I would actually recommend using default values.

However, your mileage may vary. It depends on your application. If it handles unknown values in a different way, then by all means go with that :)

Answer 2

I disagree with using a default value. The purpose of null is to show you have no information. Empty string does not mean that, it is a value. Further, if you start disallowing nulls on strings, then you need to consider other datatypes. Numbers and dates are hard to have a value that means "I don't have a value for this field." If you make the mistake of storing numbers or dates in varchar fields then putting an empty string instead of a null could result in queries that don;t work when you need to convert them to the date or numeric equivalent to do math processes on them (Not recommending storing date and numeric data as strings, just recognizing you may already have some and this scheme can cause problems with how they are queried.) If you do not allow nulls for all except these fields, you will make many, many mistakes when querying these fields becasue you won't be used to checking for nulls and will be far more likely to forget to do so. You can cause a new brand of query problems as well. Consider:

select count(myfield), myfield2 from mytable group by myfield2

This would have different results if you use null or empty string to store values when you don't know of one.

Answer 3

The main errors you'll run into, I believe, are logic errors.

Strings are easy, but integers are different. Consider a salary column. With a default of 0, this might indicate something different from a NULL. NULL means the employee is not put through payroll, while a 0 means he must still be processed from some reason.

NULLs can also be used on Foreign Keys, but it would seem to be a bad idea to have a Default on a FK.

Answer 4

I believe it's down to a matter of preference coupled with database purpose, while I'm not intricately familiar with how SQL server database stores it's data, I'd surmise that it'll make minimal practical difference, as I've used both Null entries and default strings as methods of differentiating and have never noticed much difference either way.

Answer 5

Adding null-ability to a column does add more storage overhead. A null bitmap is created for every eight nullable columns. For instance, one through eight nullable colums adds one additional byte to the row, nine through sixteen adds another byte for a total of two additional bytes per row, and so on.

Checking for NULL versus an empty string ('') is different since you would need to check COLUMN IS NULL OR COLUMN = ''. The comparison of COLUMN = NULL will not work even if COLUMN is NULL since NULL does not equal NULL, hence the IS NULL operator.

Also, concatenating or aggregating NULLs with other data will cause major problems since anything concatenated with NULL will result in NULL. Aggregations will disregard NULL and throw a warning, but this might not be the behavior you want with averages.

My advice is to stick will non-null columns and use an empty string as a placeholder if you want to minimize storage as much as possible AND you don't want the added headache of checking for NULLs in your code. I'm not suggesting this applies to every case and an argument could be made for nullability, but this approach keeps it simple.

Answer 6

Its up to you to decide and mostly its driven by the application behavior and business needs.

A default column, most of the time is used when

1. your application doesn't want to input the column at all. A good example is "audit" columns, were you don't expect the application/user to feed when the row was inserted/updated
2. used in some modules (web page/wind forms etc.,). Example, a quick signup form accepts only desired user id/name, email and password.
3. if you are NOT lazy to fill the test data manually. I find the laziness of the developers is what drives mostly to have a DEFAULT column unnecessarily.