SQL Server: Null VS Empty String

Question

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 '' ?

Answer 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.

Answer 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.

Answer 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.

Answer 4

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

Answer 5

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).

Answer 6

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.

Answer 7

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?

Answer 8

In terms of having something tell you, whether a value in a VARCHAR column has something or nothing, I've written a function which I use to decide for me.

CREATE FUNCTION [dbo].[ISNULLEMPTY](@X VARCHAR(MAX))
RETURNS BIT AS
BEGIN
    DECLARE @result AS BIT
    IF @X IS NOT NULL AND LEN(@X) > 0
        SET @result = 0
    ELSE
        SET @result = 1
        
    RETURN @result
END

Now there is no doubt.

Answer 9

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).