Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 NULL opinion?
 Forum Locked
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 4

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/23/2004 :  02:35:09  Show Profile
quote:
Originally posted by Frank Kalis

I think I mentioned in my some previous posts, that the discussion on this topic has been around for quite a long time. It will definitely not be solved here nor anywhere else, but, once again, it turned out to be very funny again.



I'm solving it this time...

-ec

;)
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

Switzerland
413 Posts

Posted - 03/23/2004 :  02:35:52  Show Profile  Visit Frank Kalis's Homepage
Good luck...



--Frank
http://www.insidesql.de
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/23/2004 :  02:37:04  Show Profile
quote:
Originally posted by mohdowais
Excellent point. If you never use NULLs how do you cater for living persons in a table that has both DateofBirth and DateofDeath, for example. If the person is not yet dead, what value do you put in the DateOfDeath column? A default value 100 years into the future? Defaults have their place, but they are no substitute for NULLs.



Why not default DateofDeath to their DateofBirth. That would give them a lifespan of 0, which could be your indicator that they are still alive.


-ec
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

Switzerland
413 Posts

Posted - 03/23/2004 :  02:37:15  Show Profile  Visit Frank Kalis's Homepage
oh, btw, if you are already working on this, maybe you can also create a better implementation of the relational model in SQL



--Frank
http://www.insidesql.de
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

Switzerland
413 Posts

Posted - 03/23/2004 :  02:38:27  Show Profile  Visit Frank Kalis's Homepage
quote:
Originally posted by eyechart
Why not default DateofDeath to their DateofBirth. That would give them a lifespan of 0, which could be your indicator that they are still alive.


Wonder if they will ever be able to get their driving license?


--Frank
http://www.insidesql.de

Edited by - Frank Kalis on 03/23/2004 02:38:47
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/23/2004 :  02:38:47  Show Profile
quote:
Originally posted by Frank Kalis

oh, btw, if you are already working on this, maybe you can also create a better implementation of the relational model in SQL



--Frank
http://www.insidesql.de



I posted earlier about tackling the surrogate key issue. Maybe we could also bust out what is better Oracle or SQL server. That one is a gem also.



-ec
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

Switzerland
413 Posts

Posted - 03/23/2004 :  02:39:29  Show Profile  Visit Frank Kalis's Homepage
What is Oracle?

--Frank
http://www.insidesql.de
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

Switzerland
413 Posts

Posted - 03/23/2004 :  02:44:40  Show Profile  Visit Frank Kalis's Homepage
Getting serious again.
This one here is a very fine comparison between DB2, Oracle and SQL Server on how they comply with the relational model http://www.handels.gu.se/epc/archive/00002948/01/Nr18_MIB.pdf

--Frank
http://www.insidesql.de
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 03/23/2004 :  03:59:44  Show Profile  Visit nr's Homepage
quote:
Originally posted by eyechart

quote:
Originally posted by derrickleggett
Am I going to make my numbers 0 since I'm making my strings empty btw??? Is that the numeric equivalent of an empty string? It isn't if you're an accountant. It's a very significant number. So do I have to define a rule for each datatype? No, I just need to know that NULL means I didn't receive anything and the value is unknown.



Yes, or it could mean that the person doing data entry simply forgot to input that value. In that case it could mean two things. Or maybe it meant that the software you use to calculate the value messed up and didn't properly insert a valid value. Or it could be a dozen other reasons. The point is NULL can mean anything, not just one thing and that is the problem.

Even though you think you can define precisely what NULL means in your table, you really can't.


-ec



null is usually used to mean unknown or not initialised which you can consider as too different things. MMaybe there should be two nulls?
You could always do this by moving the value to a subordinate table linked by the PK. For the dateofdeath example a live person would be indicated by the absence of an entry - or you could consider null as another way of holding the info.

I still hold that if you think they may cause problems then you don't have to use them.
If the reason is that you don't understand they way nulls are handled then you should learn and test situations (doesn't take long) or you will be severly limitting the systems you can interact with.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/23/2004 :  08:31:14  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
This has been fun.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 03/23/2004 :  08:35:16  Show Profile  Visit robvolk's Homepage
quote:
Like with the aggregate functions. Why doesn't AVG() evaluate NULLs? Why does COUNT()? it is inconsistent.
Count is perfectly consistent with the other aggregate functions. Count(*) is a special case. Count(expression) will not count a null in that expression. Count(*) ignores actual column values and counts rows, regardless of nulls:

create table #a(i int null)

insert #a default values
insert #a default values
insert #a default values
insert #a(i) values(1)
insert #a(i) values(1)

select count(*) AllRows, count(i) NonNullColumns, sum(i), avg(i) from #a

drop table #a
quote:
The ones running ad-hoc queries using crystal reports and tell me that they understand how to handle NULL values.
They don't know, they have to learn, just like DBA's managing the databases.
quote:
Even though you think you can define precisely what NULL means in your table, you really can't.
I can. I have a start date and a complete date. Start date is not nullable and represents when the item began work. End date is nullable and is only updated when the work is completed. Very simple, very logical, has absolutely nothing to do with whether the end date is known, unknown, forgotten to be entered, or screwy front-end interface. A null end date means the work is not yet completed, period. No mystery at all, everyone would understand that intuitively (someone who doesn't probably has a lot of trouble tying their shoes, so I'm not too concerned about the average dipshit end user) Putting in some kind of date, even a symbolic or token value, to avoid nulls will only overcomplicate a very simple thing.
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 03/23/2004 :  08:53:13  Show Profile
Me think NULL perfectly valid value for Date of Death if person living.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/23/2004 :  09:01:46  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
I think we ought to make it an obscure date so end users can ponder the endless possibilites of reverse conception.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/23/2004 :  15:01:51  Show Profile  Visit jsmith8858's Homepage
Hate to bring this up but -- what about OUTER JOINS ?? You need NULL to indicate the join didn't return matching rows from both tables.

People fear nulls because they don't understand them and they don't undertand how to use them. I agree with Nigel -- it's up to you, do what you feel you can best handle. I also agree with the main problem is the distinction between "existance of a value" and "unknown". In Rob's example, what is the best way to indicate that the project is definitely over, but you just don't know the exact end date was yet (i.e., lost paperwork or something)? I know it sounds silly, but it seems like another "status" column might be needed; of course, all that completely depends on your application and your business.

A minor thing about setting birthdate=deathdate : you then force a table scan to find out who is still alive, as opposed to saying "WHERE deathdate is Null".

Anyway, I don't mean to stir things up just to bring up a few odds and ends... enjoy the debate ....

- Jeff

Edited by - jsmith8858 on 03/23/2004 15:09:43
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 03/23/2004 :  15:56:47  Show Profile  Visit nr's Homepage
This because it is assigning two meanings to one column. Completion date meaning that the project is complete and giving the date.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 03/23/2004 :  17:44:01  Show Profile
The birthdate, enddate (like startdate, enddate) example can usually be solved by further normalisation. You automatically have a dependency on the birthdate (you can't be dead when you haven't lived..(religious beliefs not included..) and unless the birthdate is the whole key, further normalisation is possible.

Unless you have a strict performance metric, the additional performance burden of the join is (IMHO) worth it.

It seems for string columns, people use NULL because they "know" it can be interpreted as "unknown", but the same could be said of empty string or a question mark...

A possible "unknown" number could be infinity (a feature of SQL Server 2009 code name Sioux :-) ). Date types could us infinity or perhaps a represention of 1 second before the big bang.. oh wait... now that is NULL!

DavidM

"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. "
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/23/2004 :  18:01:35  Show Profile
quote:
Originally posted by u4cast

Has anyone got an opinion on this:



nah, I don't think you'll find any opinions here...



-ec

Edited by - eyechart on 03/23/2004 18:08:04
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/23/2004 :  18:33:48  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
I think every column should be it's own table. We could have customer_end_date, loan_end_date, lead_end_date, etc, etc. It would be great. :) Is end date an entity? If end date is an entitity, then to be consistent so would start date. Maybe we could just have a date table with a date type.



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 03/23/2004 :  19:12:08  Show Profile
Of course end_date is not an entity, it is column that does not rely wholey on the key and thus is able to be normalised further. And the process of normalisation would eliminate NULLs in this case.

What is so hard to fathom with that? And why go off on a tangent about confusing types and relations? The only possible gripe is performance...



DavidM

"The easiest way to give up marijuana is to smoke so much you forget that you smoke it."
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/23/2004 :  19:14:46  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
That's a rather large gripe when you average 400 t/sec

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
Page: of 4 Previous Topic Topic Next Topic  
Previous Page | Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000