NULL vs Default Value in SQL Server Database [duplicate]

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.