Wednesday, January 28, 2009

SQL Server, NULL varchar vs empty varchar

It always amazes me that people LOVE NULLable fields in database tables. I find very little value in them, as you can represent "empty" as a nullstring (zero-length string) instead, and NULLs tend to make querying the database just a little bit harder to deal with, and what we developers don't really need is for our lives to be any harder.

The only thing that I can figure is that people believe that a NULL varchar in SQL Server takes up less space than an empty NOT NULL varchar. I devised a test to see if this was true.

create table temp(data varchar(500) NULL)
create table temp2(data varchar(500) NOT NULL)

declare @counter int
set @counter = 0
while (@counter < 50000)
BEGIN
insert into temp(data)
values(NULL)

insert into temp2(data)
values('')

SET @Counter = @Counter + 1
END

exec sp_spaceused 'temp'
exec sp_spaceused 'temp2'

drop table temp
drop table temp2

This script, when run on SQL Server 2005, shows that both tables are exactly the same size, proving that NULLS and empty strings take up the exact same space on the disk.

The moral of the story is this: don't use NULLs if you think its saving you space, its a false economy.



2 comments:

  1. The value NULL means no data, emptiness, nothing, unknown, missing value, etc. The value empty string means an empty string. Confusing the NULL value and the empty string may cause data integrity problem.

    ReplyDelete
  2. By definition, an empty string...wait for it...has nothing in it.

    I am well aware of 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.

    That being said, you didn't get the point of the post. All I have on offer here is that on SQL Server, NULL and '' take up the exact same number of bytes on the disk. Hence, there is no space savings. This is because SQL Server allocates a pointer field and a length field in the row header (to which you have no direct access) to keep track of any varchar field, and they don't magically go away when the field is set to NULL, they're just zeroed.

    Furthermore, allowing NULL is a less restrictive configuration than disallowing NULL. It only follows that if any entity integrity issues are to arise, it would be from FEWER checks that the data are sound. Therefore, logically, allowing NULL should always have a good, solid reason, and disallowing NULL is a good practice.

    ReplyDelete