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.
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.
ReplyDeleteBy definition, an empty string...wait for it...has nothing in it.
ReplyDeleteI 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.