SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
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

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 03/22/2004 :  18:00:31  Show Profile
>> Logic mistake - data should be represented in tables doesn't mean that everything in tables represents data

It does if that table wants to conform to the requirements for an R-table..

>> >> UNIQUE constraints should fail on NULLs in SQL, but do not
>>Don't see how you can say that. SQL server allows one null value - other database allow many - maybe some allow none.

Because a unique constraint in R-tables only works on values, and since null is not a value should be impossible. But he actually said SQL and since SQL supports null maybe they should be allowed in a SQL unique constraint.

As you say though, documentation of their use and intended meaning is vital if you are going to use them. Personally, I have found the amount of extra work to cater for them in a enterprise environment (back-end, middle-tier, front-end, OLAP and other reporting), coupled with an increased testing cycle, make them a really expensive item that would be best avoided at (just about) all cost..

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

mark1504
Posting Yak Master

United Kingdom
103 Posts

Posted - 03/22/2004 :  18:14:37  Show Profile  Visit mark1504's Homepage  Send mark1504 a Yahoo! Message
One thing I particularly like about this site is how the merest mention of beer brings a flurry of posts :).

I'm trying to thing of an on-topic comment about NULL but I've drawn a blank, or should that be a NULL! (It must be the beer!)
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/22/2004 :  19:24:04  Show Profile
If vendors were just consistent on how NULLs are used things might be different, but there are so many inconsitencies that NULLs are just a pain. Especially in hetergenous environments and/or multi-tier environments like DavidM mentioned.

Here are a couple of examples from the Practical Issues in DB Management book I linked above.

1. Aggregate functions (SUM(), AVG(), etc.) ignore NULLs except for COUNT().
2. the expression "x IS NOT NULL" is not equal to "NOT(x IS NULL)"
3. SQL's NOT doesn't mean the same thing as "not" in natural language.

for example:

SELECT COUNT(*)
FROM customer;

COUNT(*)
--------
154168

SELECT COUNT(*)
FROM customer
WHERE salutation = 'mr';

COUNT(*)
--------
4338

SELECT COUNT(*)
FROM customer
WHERE salutation <> 'mr';

COUNT(*)
--------
6269


The sum of the last 2 queries does not equal the first.



-ec

Maybe we should move on to some other topic, like the pros/cons of using surrogate keys..

;)

Edited by - eyechart on 03/22/2004 19:31:05
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/22/2004 :  19:38:19  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
I feel like I have a giant NULL where I missed this thread.

MeanOldDBA
derrickleggett@hotmail.com

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

X002548
Not Just a Number

15586 Posts

Posted - 03/22/2004 :  21:10:40  Show Profile
quote:
Originally posted by eyechart
Maybe we should move on to some other topic, like the pros/cons of using surrogate keys..



OK If you like, but understand whats happening here...

You can't reference a value that doesn't exists...

That's why your results do that...

You can not evaluate null

because it's not there, even if the row is

Yes?



Brett

8-)
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 03/22/2004 :  21:28:40  Show Profile  Visit nr's Homepage
>> >> Logic mistake - data should be represented in tables doesn't mean that everything in tables represents data

>> It does if that table wants to conform to the requirements for an R-table..

I was pointing out the erroneous logic leap made from a all data represented by table values to all data in tables representing data.
There may be definitions that preclude values being null but that isn't one of them.

==========================================
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/22/2004 :  21:31:09  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
Of course you can evaluate NULL. You can evaluate any piece of data that resides in SQL Server. NULL is a value. It's a value that represents an unknown value; however, it is still a value.

SELECT COUNT(*)
FROM customer

SELECT COUNT(*)
FROM customer
WHERE customer IS NOT NULL

SELECT COUNT(*)
FROM customer
WHERE customer IS NULL

Statement 2 + 3 = 1
--Therefore NULL has been evaluated.


SELECT COUNT(*)
FROM customer
WHERE salutation = 'mr'

COUNT(*)
--------
4338


SELECT COUNT(*)
FROM customer
WHERE salutation <> 'mr'

COUNT(*)
--------
6269

SELECT COUNT(*)
FROM customer
WHERE salutation IS NULL


Count of last three will equal total
--Therefore NULL has been evaluated.

We have now not only referenced a value that "doesn't exist". We have defined the scope of records that have an unknown value in the salutation column. This in itself can provide statistical value on the quality of our data and the metrics that drive our business.

Any data, including the unknown NULL, can be evaluated in SQL Server. Not understanding or abusing it is easy and dangerous though.

I seem to be on a theme tonight.

Don't you just want to kill me.

MeanOldDBA
derrickleggett@hotmail.com

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

X002548
Not Just a Number

15586 Posts

Posted - 03/22/2004 :  21:40:26  Show Profile
quote:
Originally posted by derrickleggettDon't you just want to kill me.



Nah...I love a good bar fight....hockey player and all...

but you're not evaluating (null), your evaluating if the value exists or not...

that's different....

and I'm trying to do this in a multi-margarita-fog......


I'll have to re=read this AM

zzzzzzzzzzz



Brett

8-)
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/22/2004 :  21:46:58  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
but you're not evaluating (null), your evaluating if the value exists or not...

Actually, I've evaluated (null) and the fact that the value exists or not. Reread and think about it.

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/22/2004 :  22:01:41  Show Profile
I think you have "catered" for null is about all you can say...

It is telling that you cannot use the equality operator ("=") for null expressions but must use the IS keyword..

Not all operatorions treat null like values...


create table TestNull(Data varchar(50) null )
go
insert TestNull
	Select '1'
	union all 
	select null
go
--NULL treated like value
select distinct data
from testnull
go
--Null not treated like a value
select data, Count(data)
from testnull
group by data
GO
drop table testnull


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

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/22/2004 :  22:13:16  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
If understanding how my language works is catering then so be it. Null is still a value that can be evaluated, analyzed, and abused, just like any other value.

create table TestNull(Data varchar(50) null )
go
insert TestNull
Select '1'
union all
select null
go
--NULL treated like value
select distinct data
from testnull
go
--Null not treated like a value
select data, Count(ISNULL(data,1))
from testnull
group by data
GO
drop table testnull


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/22/2004 :  22:49:18  Show Profile
>>> Null is still a value that can be evaluated, analyzed, and abused, just like any other value.

I'd like to know how you can abuse the value 1 or 'Dingo'...

A value requires a type.. null does not have a type...

A specific question..
For any string column, why would you choose a NULL over a empty string?

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

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/22/2004 :  22:52:18  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
One lead vendor didn't provide the data at all. The other company did with an empty string for when it wasn't filled out. You paid $30 more per lead for the ones with the value. The profit on the leads with the string filled was %50 more valuable then without. But the first two times they sent you leads, they didn't send any. Should you take leads from them without the value? How do you know who sent and who didn't? Is empty known if they never sent it or unknown? Gee, I don't know. You tell me.

If you control what constitutes a NULL on the lead table you increase the value of the leads and what analysis you can do on the leads. NULL is not received. '' is empty. value is value. Make sense?

NULL is a value of unknown. That value = $$$.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Edited by - derrickleggett on 03/22/2004 23:00:13
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 03/22/2004 :  23:54:48  Show Profile
I am struggling to understand what you just wrote..

So one vendor sends you a null and doesn't get paid (because null isn't a value?), while the other sends you an empty string and gets paid?

>>if you control what constitutes a NULL on the lead table you increase the value of the leads and what analysis you can do on the leads. NULL is not received. '' is empty. value is value. Make sense?

Partially.. but haven't you been arguing that null is a value? (value is value is null) What is stopping you from using empty string as the "rejection" value? And why choose one over the other (my original concern)?

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

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/23/2004 :  00:58:29  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
Because null being an unknown is a value when you are analyzing data. An empty string just means you got back empty data. Null means it didn't exist in the first place. Why would I put an empty string in there if I didn't get anything in the first place????

The value isn't empty. Empty is a value. I got nothing back. Zilch. They never tried to send me anything. I didn't set it to blank. The status of that field as it relates to the entity is completely unknown. Why would I throw something in to complicate the issue? They didn't send me a blank, they sent me nothing.

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.

In SQL Server I have to then know how to handle NULL. There's no way around it. Learn how to handle NULL because it's a reality in SQL Server.

MeanOldDBA
derrickleggett@hotmail.com

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

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/23/2004 :  02:17:00  Show Profile
NULL is not a value. The point of my last post was to illustrate that most databases erroneously evaluate NULL as a value some of the time.

Like with the aggregate functions. Why doesn't AVG() evaluate NULLs? Why does COUNT()? it is inconsistent.




-ec

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

mohdowais
Sheikh of Yak Knowledge

United Arab Emirates
1456 Posts

Posted - 03/23/2004 :  02:21:55  Show Profile  Visit mohdowais's Homepage
quote:

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.


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.

Just my 2 fils.

OS
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/23/2004 :  02:29:27  Show Profile
quote:
Originally posted by derrickleggett
SELECT COUNT(*)
FROM customer

SELECT COUNT(*)
FROM customer
WHERE customer IS NOT NULL

SELECT COUNT(*)
FROM customer
WHERE customer IS NULL

Statement 2 + 3 = 1
--Therefore NULL has been evaluated.


SELECT COUNT(*)
FROM customer
WHERE salutation = 'mr'

COUNT(*)
--------
4338


SELECT COUNT(*)
FROM customer
WHERE salutation <> 'mr'

COUNT(*)
--------
6269

SELECT COUNT(*)
FROM customer
WHERE salutation IS NULL


Count of last three will equal total
--Therefore NULL has been evaluated.



Again, I am very familiar with how this works. However, think about your end users. The ones running ad-hoc queries using crystal reports and tell me that they understand how to handle NULL values.

Do you wrap every SELECT argument with ISNULL or NVL? I didn't think so.


-ec
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

Switzerland
413 Posts

Posted - 03/23/2004 :  02:32:32  Show Profile  Visit Frank Kalis's Homepage
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 strongly suggest reading this
http://www.baetzler.de/humor/null.html

This gives really deep insights into NULL and what smart brains can do with it.




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

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/23/2004 :  02:34:16  Show Profile
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
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-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000