Thread: Whats preferable in field - null or empty?

Hi people
I was wondering if Null values should be allowed in db fields or not?

I dont allow Null values in database, i enter "empty" rather than Null.

But i think when there is a large amount of data/records, then these "empty" entries might add to db size/burden.

Right now, i insert "empty" for text fields, 0 as default for integer/float types and always some value in datetime fields.

Your thoughts please
Thanx

Thanx don
I dont enter the word "empty"....... i just enter "" (sorry, i didnt explain it well), meaning the value in column is empty, not null

I was just asking in terms of database advantage/disadvantage

M,

This is one of those heavily debated topics. Have a look at this blog (particularly all the comments): Why NULL Values Should Not Be Used in a Database Unless Required

It is my opinion that NULL has its place in database design. Keep in mind that NULL means Unknown, whereas an empty string means that it is Known, but not entered as a value. I personally avoid using NULL. In fact, the database in my current project is designed to not accept NULL. Supposedly, NULL takes less space than an empty string, but I'm not sure how much you're actually saving.

My opinion too J
I also avoid NULL values in my database structure nad never enter null in database.

I'll read the blog for more

In MS SQL, a null value takes no space -- rather a NULL bit is set in the record header or the field. If you have a table with no null field, you'll save this 8-bit field, otherwise, you can have up to 8 null fields with no penalty.

Storing an empty string in a variable length field takes at least 2-bytes for the length. If you have millions of records, this could be significant.

NULLS do have their place tho -- if the data they represent is optional, such as a ShipToAddress, then NULL seem appropriate since it accurately represents that no data was supplied. However, I agree that NOT NULL should be the default unless you have a very good reason to make the field NULLable

I think NULL should be used when it's appropriate as you all have mentioned. It indicates when a value is intentionally blank, and that can be very useful.

My shop has another reason to allow nulls. Our in-house software has its' own persistence model that requires it to understand the schema of the database. Basically it's an XML schema. If a change is made to the SQL server schema before the code is updated, the application would fail unless the new field allows null. However, the database update MUST occur before the code is deployed or it would fail because of the missing columns... it's a chicken and egg scenario. The only way to add a NOT NULL column to our production database is to create the column as nullable, update the data to the default and then update the column to NOT NULL.

As Don said, it's probably good practice to write your code with the possibility of nulls being considered. As for me, I use the NULLS to my advantage. It's easy to eliminate them

Thanx Wolffy and Laura
I am not worried about handling Null, but i was more concerned about the database space utilization difference between Null and empty string.......... which for say thousands of records wont be much (right?).

Anyway, i think i can live with Empty strings in db rather than Null as of now.

okay ... i'm thinking i shouldn't take classes, but just as a point of fact, null values are considered in calculating storage requirements if the field is a fixed length datatype such as int, char, money, etc. it is not included, however, when using varchar. for example ... if you have 10 columns in your db and they are all fixed length datatypes, you would have to account for 2B of space. although minimal, it is still counted.

see ... i am learing some things.

btw ... empty strings take up more space than null values.

Originally Posted by mehere

btw ... empty strings take up more space than null values.

Ya, i sensed it

Thats the reason i raised this issue.
So i am still not sure of allowing Nulls in database