Null or empty string in a database

[Jay]

For a column that contains a string (let's say varchar[50]), is there any
performance advantage in not allowing nulls, and using an empty string ("")
to instead?

 

[Michael Starberg]

For a column that contains a string (let's say varchar[50]), is there any
performance advantage in not allowing nulls, and using an empty string
("")
to instead?

For perfomance, I have no idea. Probably none.

But I must confess I've done a not null on a varchar
- just to make sure I always get a string and not a null from the database.

I work a lot with ASP.NET and sporting a string as a null,
is a no no. It makes your whole webpage goes kaboom.

Enforcing rules on a database just to have your C# easier
is wrong in my opinion. But very safe in .NET 1.1

For .NET 2.0 and above, it is just stupid as you can always do:

string s = MyData[col, row].SomeValue ?? "";

....

As a sidenote, remember the thread on "" and String.Empty?
Here is a good example why "" is sometimes better...

1) string s = MyData[col, row].SomeValue ?? "";
2) string s = MyData[col, row].SomeValue ?? String.Empty;


1) readable, we want SomeValue
2) too verbose, if you read it fast, you might be looking for SomeEmpty
below. *s*

- Michael Starberg

 

[Guest]

In SQL Server, the is a performance advantage to search for col1 is null
rather than col1 = ''

This is because there is a flag in the row header for each column indicating
whether its null so it is easier to check this flag then do a string
comparison

HTH

 

[Jay]

Thanks Ciaran,

Yes, I see what you mean, although presumably if the column isn't nullable,
SQL Server wouldn't have this flag, so wouldn't need to check it first. I
assume that checking it wastes time for strings that aren't null.

Jay

message In SQL Server, the is a performance advantage to search for col1 is null
rather than col1 = ''

This is because there is a flag in the row header for each column indicating
whether its null so it is easier to check this flag then do a string
comparison

HTH

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

For a column that contains a string (let's say varchar[50]), is there any
performance advantage in not allowing nulls, and using an empty string ("")
to instead?

In many cases NULL and "" would mean something completely
different.

I would be very reluctant to reduce data reliability for
a performance gain I doubt exist.

Arne