(NOT) NULL for NVARCHAR columns

Question

Allowing NULL values on a column is normally done to allow the absense of a value to be represented. When using NVARCHAR there is aldready a possibility to have an empty string, without setting the column to NULL. In most cases I cannot see a semantical difference between an NVARCHAR with an empty string and a NULL value for such a column.

Setting the column as NOT NULL saves me from having to deal with the possibility of NULL values in the code and it feels better to not have to different representations of "no value" (NULL or an empty string).

Will I run into any other problems by setting my NVARCHAR columns to NOT NULL. Performance? Storage size? Anything I've overlooked on the usage of the values in the client code?

Answer 1

A NULL indicates that the value in the column is missing/inapplicable. A blank string is different because that is an actual value. A NULL technically has no data type where as a blank string, in this case, is nvarchar.

I cant see any issues with having default values rather than NULL values.

In fact, it would probably be beneficial as you wouldn't have to worry about catering for NULL values in any of your queries

e.g

Select Sum(TotalPrice) as 'TotalPrice' From myTable Where CountOfItems > 10

much easier than

Select Sum(IsNull(TotalPrice,0)) as 'TotalPrice' From myTable Where IsNull(CountOfItems,0) > 10

You should use default constraints in your DDL to ensure that no rogue NULL's appear in your data.

Answer 2

The concept of the NULL value is a common source of confusion. NULL is not the same as an empty string, or a value of zero.

Conceptually, NULL means "a missing unknown value" and it is treated somewhat differently from other values. For example, to test for NULL, you cannot use the arithmetic comparison operators such as =, <, or <>.

If you have columns that may contain "a missing unknown value", you have to set them to accept NULLs. On the other hand, an empty string simply means that the value is known, but is empty.

For example: If a "Middle Name" field in a "Users" table is set to NULL, it should mean that the middle name of that user is not known. The user might or might not have a middle name. However, if the "Middle Name" field is set to an empty string, this should mean that the user is known to have no middle name.