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.