[sql-server] SQL Server: Null VS Empty String

How are the NULL and Empty Varchar values stored in SQL Server. And in case I have no user entry for a string field on my UI, should I store a NULL or a '' ?

NULL is a non value, like undefined. '' is a empty string with 0 characters.
The value of a string in database depends of your value in your UI, but generally, it's an empty string '' if you specify the parameter in your query or stored procedure.

How are the "NULL" and "empty varchar" values stored in SQL Server. Why would you want to know that? Or in other words, if you knew the answer, how would you use that information?

And in case I have no user entry for a string field on my UI, should I store a NULL or a ''? It depends on the nature of your field. Ask yourself whether the empty string is a valid value for your field.

If it is (for example, house name in an address) then that might be what you want to store (depending on whether or not you know that the address has no house name).

If it's not (for example, a person's name), then you should store a null, because people don't have blank names (in any culture, so far as I know).

An empty string is a string with zero length or no character. Null is absence of data.

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.

NULL values are stored separately in a special bitmap space for all the columns.

If you do not distinguish between NULL and '' in your application, then I would recommend you to store '' in your tables (unless the string column is a foreign key, in which case it would probably be better to prohibit the column from storing empty strings and allow the NULLs, if that is compatible with the logic of your application).

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.

if it's not a foreign key field, not using empty strings could save you some trouble. only allow nulls if you'll take null to mean something different than an empty string. for example if you have a password field, a null value could indicate that a new user has not created his password yet while an empty varchar could indicate a blank password. for a field like "address2" allowing nulls can only make life difficult. things to watch out for include null references and unexpected results of = and <> operators mentioned by Vagif Verdi, and watching out for these things is often unnecessary programmer overhead.

edit: if performance is an issue see this related question: Nullable vs. non-null varchar data types - which is faster for queries?