Database.ca SQL Server Blog

My Blog

Monday, March 03 2014

Keywords: empty string, '', null, sql server, space differences comparison file size, take more space, bigger, more memory

Does an empty string value in a varchar column occupy more space than NULL?

A friend called me up today to ask me an interesting question:

 

"Can you tell me if there is a difference between the space occupied by NULL value in a varchar field vs Empty Strings ('') ?"

 

A quick test in SQL Server 2012 proves that the answer to this question is No. They occupy the same space.

 

empty string, '', null, sql server, space differences comparison file size, take more space, bigger, more memory

To prove the point with a simple exercise:
 
1) Let's create two tables within the same database.
 
2) Fill each table with a large number of records. in my test I inserted 525000 records in so that even a slight delta of 1 bit per row on either side would actually reflect in the stats.
 
3) Ran a query to see the space used by either table.

 

empty string, '', null, sql server, space differences comparison file size, take more space, bigger, more memory

4) Created a non-clustered index on the varchar field.
 
5) Ran a query to see the space used by either table again...just in case.

 

empty string, '', null, sql server, space differences comparison file size, take more space, bigger, more memory

When discussing NULL vs Empty String...the discussion should not be about the required disk space but rather it needs to lean toward practical differences and the potential large number of bugs that could occur due to the use of NULL...especially when it comes to joins, aggregations and comparisons.

 

empty string, '', null, sql server, space differences comparison file size, take more space, bigger, more memory

Of course, a T-SQL developer coming from the realm of Oracle could say that this is all normal and that we haven't proved a thing...since in Oracle there is no notion of empty string and all '' is intrinsically converted to Null. But SQL Server does have both entities. To prove this I ran a select distinct on both tables.

 

 

 

 

Written by Ramin Haghighat