Empty string in char,varchar or text

[DBAStriker]

How wasteful is it to leave empty string in column with data type of char, varchar, text, etc...?

I rather make them null rather than empty string for consistancy sake, but does empty string in text data type take up significant amount of memory,etc..?

[Rawhide]

There are no memory issues with storing empty columns. I prefer to use Nulls for another reason however.

Storing empty values can you give false results in a query. Two empty fields are considered to be equal but two null fields are not.

Take this query for example:

Select 1
Where '' = '' -- <-- That's 4 single quotes, not 2 double quotes

It will return 1 because it considers the two empty values to be equal. Change the = to <> or !=, and it returns 0 records.

The following three queries, on the other hand, will always return 0 records:

Select 1
Where Null = Null

Select 1
Where Null <> Null

Select 1
Where Null != Null


Here's a demonstration that shows how an empty field in a column used in a join can really mess you up!!!

I'm setting nocount on so that the results are easier to read.


Set nocount on

Declare @test1 table (testfield1 varchar(10) null)
Declare @test2 table (testfield2 varchar(10) null)
Declare @test3 table (testfield3 varchar(10) null)
Declare @test4 table (testfield4 varchar(10) null)

-- The below inserts are using 2 single quotes, not 1 double quote
Insert Into @test1 Values ('')
Insert Into @test1 Values ('')
Insert Into @test1 Values ('')

Insert Into @test2 Values ('')
Insert Into @test2 Values ('')
Insert Into @test2 Values ('')

Insert Into @test3 Values (Null)
Insert Into @test3 Values (Null)
Insert Into @test3 Values (Null)

Insert Into @test4 Values (Null)
Insert Into @test4 Values (Null)
Insert Into @test4 Values (Null)

Select count(*) As EmptyCount
From @test1 As t1
Inner Join @test2 As t2 On t1.testfield1 = t2.testfield2

Select count(*) As NullCount
From @test3 As t3
Inner Join @test4 As t4 On t3.testfield3 = t4.testfield4

Set nocount off


The above queries return the following results:

EmptyCount
9

NullCount
0