What's the difference between saving a null and a zero

[ryandoah]

Hi All,

I sent some DB design specs to our "DB" guy. In the specs are some fields that may or may not have values inserted into them, ie; if data is supplied for a field it is inserted, if not, no insertion to that field. I had sent the spec stating that several of the fields were to allow nulls, since I didn't know if they would have data supplied to them or not. Our "DB" guy decided it would be more efficient to not allow nulls into any of the values, overriding me and opting to give them a default value of zero. This kind of erks me for several reasons. First, being a Comp Science BA graduate, I fully understand the difference between what a zero is and what a null is, thus I set this up to allow nulls for fields that may not have any info, not ones that have have zero info. Secondly, I was under the assumption that a zero took more space to save than a null, in SQL Server, granted not much individually, but possibly a great bit on the large scale. Am I wrong? What is the size difference between saving a null and a zero? Also, if the data type is Currency, wouldn't it make since to have the default save as 0.00, than just plain 0? Or am I just being nitpicky.

Thanks,
ryandoah

 

[dbomrrsm]

You arent being "nitpicky" in my opinion - null is null - null does not equal null and, null does not equal 0 - depending on what the data is going to be used for it can really mess up your results.

AVG of 40, 50, 60 = 50
AVG of 40, null, 60 = 50
AVG 40,0,60 = 33

[blue]DBomrrsm[/blue]

 

[mrdenny]

I also agree. Null is null. 0 is a value and therefor not null. Storing a value of 0 takes one byte (assuming a varchar data type, numeric datatypes are different). I'm not sure what the space requirments are for null. Space shouldn't be a big deal with the size of current hard drives.

Currency actually has a precission of 4 not 2. Microsoft when designing this data type decided to allow for tenths and hundredths of pennies for POS systems, banks, etc. From what I remember no matter what you enter into the field, it is storred with the 4 characters of precissions.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLSister]

There is a theory among some dbas that nulls are bad. And yes you do have to account for them in code that might contain calculations. Sometimes it will break the code altogether if you do not. This makes writing code much more complex and can slow the efficiency of the code if it must check for nulls and replace them with a value in order to perform calculations.

However, there is a genuine differnce between 0 as a value and null as I don't currently know what the value is. For instance in a price field, how would you then distinguish between the items that are free (usually items that are only supplied if you order something else) and those for which you don't have the price (the ones which say call for price on the price list). Null is the proper way to do that.

Is it important to have the null values allowed? That depends on the situation. If you never have any genuine 0 values, it could be more efficient to store 0 as a value if you don't know the value. Most of your code will be written to count the value as zero anyway. And it certainly will make code writing simpler. But if there are genuine 0 values, you lose the ability to distinguish between them and the records where the value is unknown. Whether you need to do this is what you have to consider.

Personally I would talk to the DBA and ask him or her why the change was made and see what reasons you are given. Then present your argument for why they field needs to allow nulls if you are not swayed by the DBA's reasons.



Questions about posting. See faq183-874

 

[ryandoah]

Thanks all.

I really doubt I'll win the battle, but I'll ask him about it. I am really just looking for knowledge on the situation, and ya'll have backed my thoughts. My job is not very conductive to professional development, so I must depend on you guys for level headed answers to my questions. Thank you. I just think it's a horrible misrepresentation of the data, as depending on what choices the user makes, only certain fields will have data entered into them, and the others will never have data entered into them, if certain other fields are filled.

Basically, I have 7 fields, but because of the different branches on this logic tree, only a max of 2 or 3 will be filled at a time. The rest will remain null data, which will never be queried. I guess this is too be expected from the same people that brought me a DB that uses foreign key values, throughout, that don't even exist in lookup tables.

Thanks again,
ryandoah

 

[ryandoah]

FYI, I sent this question to the db guy.

I was just curious as to why you decided that it would be better to have some of the EFile field values default to 0, vice defaulting to null, as I had spec'd? The values and fields to be inserted to are variable dependant upon the choices that are made by the user, ie; some fields will not have any data sent to them at all, hence null data, not 0 data. I understand that having null values can mess up certain methods of calculation, but in this case the action ID will govern which fields need to be queried from in the table. Personally, I feel that it is a misrepresentation of the data, since now some fields that should contain no data (null) will contain 0 data which is a numeric value, but like I said, the actionID will dictate which fields are drawn back out of that table, so I guess, technically, it really doesn't make that much difference, since either way currency values are saved as 8 bytes in SQL Server.

 

[r937]

Our "DB" guy decided it would be more efficient to not allow nulls into any of the values, overriding me and opting to give them a default value of zero.

this person is blowing smoke out his nether regions and should be shot

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ryandoah]

r937,

This is what I'm thinking too. I thinking that he is doing it so that he doesn't have to write a specialized script for exporting each of the different values sets from the table, which to me sounds just plain lazy, and will most probably slow the overall query speed. Granted, we may be only talking milliseconds here, but I just don't like the implication that my design specs are wrong, due to someone else's ignorance or lack of ambition. We'll see what his answer is, if he gives me the courtesy of an answer.

 

[KingRichard3]

And what was the conclusion with the [tt]DBA GUY[/tt]???


Rick

 

[Catadmin]

And what, I wonder, does he think a 0 value means in a character field such as LastName or EmailAddress?

Boy, all those people with an email address of 0. Can you imagine how many error messages your Exchange Server would get if you tried to send an email to them? As opposed to not sending out an email because a Null value indicates they don't have an email address. @=)

Please share what happened next. @=)



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[ryandoah]

Well, I never got an answer. So I sent the question again, response receipt, still no answer. The guy is a contractor, and answers only to my supervisor, so I went to her, to get the question relayed. She said something along the lines of "I don't know what all this animousity between you two is about, but it needs to stop." End of Discussion. So I'm guessing he beat me to her with whatever case he presented. So the crappy (In my eyes) design stayed, and I never got any answer. Quite disheartening.

Thankfully, at least, all the fields in question are currency fields, so I guess nothing will be damaged by this. It just the principle involved, I guess, that bothers me. Well, and having my questions shunned. That, and I still don't know if there is a storage size difference between saving a zero or a null, but I think it is a misrepresentation of the data.

Since I've started working here, I've found numerous minor problems with the database, asked about most, and been rejected a decent answer everytime, so I've pretty much given up on asking, which sucks, cause I like being involved and knowledgable of the entire system. It's not outside of my capability, and it effects my performance also.

Here are just a few things that I find "iffy", at best.

1) Foriegn key values used throughout the DB that don't exist in the look-up tables. Disorganization.

2) Work around entries in many tables that don't match at all the data involved, including but not limited to, blank entries. All of which I have to filter for in order not to mess up my recordsets. Disorganization that slows my queries.

3) Using improper default values that are not matched to the specific task the data represents. Ignorance or Laziness

4) Using a replication standard across 17 offices which henders the development of the entire system due to its inability to adapt. We're talking talking a mammoth DB here, it decent sized, but over-abundent, could easily be housed in one office, with a spare office for redundancy. I mentioned this one when I first started, needless to say, I didn't make any friends. Interestingly though, a year or so later(recently) DB Guy has this great idea, "Let's drop replication, and use one DB in one office, with one for redundancy." I don't even know what to say about this one.

5) Designing foriegn key tables that run backwards of what logic dictates and don't even plug into the tables they should.

6) And my personal favorite, and I wish you could see the schema diagram I made of this thing, foriegn keys relationships that do figure eights through the tables. I am not absolutely positive the this is incoreect, but I've never seen anything like it. Certainly can't be proper design logic. I don't know how you can make a DB with 30 someodd tables in it and never once think about creating a diagram, even if you have to do it with pencil and paper, but I had to make the diagram for myself, and found some interesting relationships, that's for sure.

Wow, I'm glad you asked, Rick. Appearently, this has really been bothering me subconciously, I feel much better now. Thanks. Please, feel free to comment on any of this, as I am curious as to just how much I am wrong about.

ryandoah

 

[ryandoah]

Correction - We're not talking a mammoth DB here, it's decent sized, but not over-abundent, it could easily be housed in one office, with a spare office for redundancy.

 

[r937]

ryandoah, start looking for a new job

that place is a disaster waiting to happen

that "DB guy" and your supervisor should both be taken out back and shot

note: you will need to work on your "personal interaction" skills so that there is no chance that it will be interpreted as animosity -- it's easy to make a charlatan look like an idiot, but not if it results in your boss reacting the way you have described

unfortunately, given what's already transpired, it's unlikely that you can correct the situation without making the "personality conflict" worse than it already is

keep your head down, keep your mouth shut, do your best, and quietly look for another job

and when you leave, don't say a word, just smile and leave



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[ryandoah]

I think you may be right. The main reason I haven't made it into a pursuable issue is because the time difference we both have in the field. I have been into web design for about 4-5 years now, and have a BA in Information Science, specializing in web design, with a minor in CS. This guy, who I don't believe is classically trained, has been programming and what not for, I believe, 20 something years. So where as I have the nuts and bolts, he has the experience. I'm not trying to discount those without degrees, but you do get taught quite a bit of theory, which could be missed otherwise. Though, I do agree, the place is a powderkeg.

 

[r937]

if buddy has been programming for 20 years and he calls himself a "db guy" and hasn't learned yet how to make sure a foreign key actually exists in the lookup table it references, then he's even a bigger idiot than it appeared at first

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[ryandoah]

ROFL. Hakuna Matada.

 

[KingRichard3]

ROFL. Hakuna Matada.

???
Rick

 

[Catadmin]

Lion King reference, Rick. @=)

BTW, Ryandoah, you have my sympathies for your problem, but I agree with the others. If the contractor in your organization is being given more weight then you (I'm a contractor myself) and you're not being given a chance to even address the issue, then I think it's past time you found a job where your knowledge of databases gets a little more respect.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[ryandoah]

Sorry, this working off the recommendation to ->

keep your head down, keep your mouth shut, do your best, and quietly look for another job

and when you leave, don't say a word, just smile and leave

Hakuna Matada - a phrase from the afore named song from Disney's "The Lion King", it runs bacisally along the same lines of "Don't Worry, Be Happy

 

[r937]

Hakuna Matata

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)