SQL Server: Null VS Empty String

Solution 1:

There's a nice article here which discusses this point. Key things to take away are that there is no difference in table size, however some users prefer to use an empty string as it can make queries easier as there is not a NULL check to do. You just check if the string is empty. Another thing to note is what NULL means in the context of a relational database. It means that the pointer to the character field is set to 0x00 in the row's header, therefore no data to access.

Update There's a detailed article here which talks about what is actually happening on a row basis

Each row has a null bitmap for columns that allow nulls. If the row in that column is null then a bit in the bitmap is 1 else it's 0.

For variable size datatypes the acctual size is 0 bytes.

For fixed size datatype the acctual size is the default datatype size in bytes set to default value (0 for numbers, '' for chars).

the result of DBCC PAGE shows that both NULL and empty strings both take up zero bytes.

Solution 2:

Be careful with nulls and checking for inequality in sql server.

For example

select * from foo where bla <> 'something' 

will NOT return records where bla is null. Even though logically it should.

So the right way to check would be

select * from foo where isnull(bla,'') <> 'something' 

Which of course people often forget and then get weird bugs.

Solution 3:

The conceptual differences between NULL and "empty-string" are real and very important in database design, but often misunderstood and improperly applied - here's a short description of the two:

NULL - means that we do NOT know what the value is, it may exist, but it may not exist, we just don't know.

Empty-String - means we know what the value is and that it is nothing.

Here's a simple example: Suppose you have a table with people's names including separate columns for first_name, middle_name, and last_name. In the scenario where first_name = 'John', last_name = 'Doe', and middle_name IS NULL, it means that we do not know what the middle name is, or if it even exists. Change that scenario such that middle_name = '' (i.e. empty-string), and it now means that we know that there is no middle name.

I once heard a SQL Server instructor promote making every character type column in a database required, and then assigning a DEFAULT VALUE to each of either '' (empty-string), or 'unknown'. In stating this, the instructor demonstrated he did not have a clear understanding of the difference between NULLs and empty-strings. Admittedly, the differences can seem confusing, but for me the above example helps to clarify the difference. Also, it is important to understand the difference when writing SQL code, and properly handle for NULLs as well as empty-strings.