Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nulls


Nulls

Author
Message
Mike Dougherty-384281
Mike Dougherty-384281
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 944
mtillman-921105 (12/14/2010)
Good question - thanks! NULLs can be hazardous, they should come with a warning.


In the case of "Null value is eliminated by an aggregate or other SET operation" the trick is to use nulls without the warning.

hint: set ANSI_WARNINGS OFF (and accept responsibility for the consequences)
phil.wood 94423
phil.wood 94423
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 65
SanDroid (12/14/2010)
phil.wood 94423 (12/13/2010)
SanDroid (12/10/2010)
phil.wood 94423 (12/10/2010)

My default schema is not dbo!


Most QOTD readers that actually run or test the QOTD scripts do so in a default NON Production installation of SQL server that is easily rebuilt. Think Virtual PC with a Snapshot, or a server with a good Ghost backup image.

Also a database where you have DBO schema access is prefered and taken for granted, unless otherwise stated in the script or example code.

Good thing the QOTD was not about a script to clear all event and server logs and clean the DMV's of historical data... Cool


Isn't it normal to have development environments, virtual or otherwise, set up as similarly as possible to production? It should be.


My point is don't blame the author just becuase you do not understand how his script will run in your custom environment.

Of course your Dev environment should match your prod.
But I would not run any QOTD code, or any code from the internet, in a database supporting Development of a specific application either.


Your point is taken but the script was tiny and quite clearly benign. Your comment "you do not understand how the script will run" defies belief. Notifications off. Over and out.
mtillman-921105
mtillman-921105
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 3852
Mike Dougherty-384281 (12/14/2010)
mtillman-921105 (12/14/2010)
Good question - thanks! NULLs can be hazardous, they should come with a warning.


In the case of "Null value is eliminated by an aggregate or other SET operation" the trick is to use nulls without the warning.

hint: set ANSI_WARNINGS OFF (and accept responsibility for the consequences)



My solution is SET myColumn = ISNULL(myColumn, 0) everywhere. NULLs are more trouble than they're worth more often than not when dealing with financial data. ;-) (Yes, I know that a NULL can mean that the data's not right. But more often than not, a zero tells me the same thing anyway.)

______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8319 Visits: 11552
mtillman-921105 (12/15/2010)
Mike Dougherty-384281 (12/14/2010)
mtillman-921105 (12/14/2010)
Good question - thanks! NULLs can be hazardous, they should come with a warning.


In the case of "Null value is eliminated by an aggregate or other SET operation" the trick is to use nulls without the warning.

hint: set ANSI_WARNINGS OFF (and accept responsibility for the consequences)



My solution is SET myColumn = ISNULL(myColumn, 0) everywhere. NULLs are more trouble than they're worth more often than not when dealing with financial data. ;-) (Yes, I know that a NULL can mean that the data's not right. But more often than not, a zero tells me the same thing anyway.)


Wow!

And how do you then distinguish a "missing data" 0 from a "number zero" 0?

BTW, if I'm running AVG over a column, I'd rather have a warning and the average of all non-missing values, than a number that is way too low because the missing values are replaced with 0.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
mtillman-921105
mtillman-921105
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 3852
Hugo Kornelis (12/15/2010)
mtillman-921105 (12/15/2010)
Mike Dougherty-384281 (12/14/2010)
mtillman-921105 (12/14/2010)
Good question - thanks! NULLs can be hazardous, they should come with a warning.


In the case of "Null value is eliminated by an aggregate or other SET operation" the trick is to use nulls without the warning.

hint: set ANSI_WARNINGS OFF (and accept responsibility for the consequences)



My solution is SET myColumn = ISNULL(myColumn, 0) everywhere. NULLs are more trouble than they're worth more often than not when dealing with financial data. ;-) (Yes, I know that a NULL can mean that the data's not right. But more often than not, a zero tells me the same thing anyway.)


Wow!

And how do you then distinguish a "missing data" 0 from a "number zero" 0?

BTW, if I'm running AVG over a column, I'd rather have a warning and the average of all non-missing values, than a number that is way too low because the missing values are replaced with 0.


In the last few years of working here, I've used AVG only a handful of times. I typically divide totals with other totals to get averages. There is an assumption that has to be made that the data is there for most of my work; e.g., total hours worked for office x in y time frame = ? When summing those hours, the last thing I need to worry about are NULLs. But I do work in a warehouse with the data has been verified and massaged. I work in finance and if something isn't right, it doesn't balance, so that helps too. But if I want to add column A to column B in my temp table, I sure don't want NULLs tripping me up. So in my circumstances, NULLs hurt more than help.

The opposite could also be asked... Why do you have missing data?

______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8319 Visits: 11552
mtillman-921105 (12/15/2010)
The opposite could also be asked... Why do you have missing data?

Because incomplete data is a fact of life.
Companies that only do business when all data their database has a column for is known, will soon not do business anymore. Many fields are mandatory, but not all are.

If a company collects data about household situation and income of their customers, for statistical analysis and maybe some data mining and targeted advertising, and I leave the fields for number of children and monthly household income blank, would you really enter both as 0? I do have children. And I do have income. I just refuse to share that information with companies that have no need for this information, and no legal basis for asking me about it.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
mtillman-921105
mtillman-921105
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 3852
Hugo Kornelis (12/15/2010)
mtillman-921105 (12/15/2010)
The opposite could also be asked... Why do you have missing data?

Because incomplete data is a fact of life.
Companies that only do business when all data their database has a column for is known, will soon not do business anymore. Many fields are mandatory, but not all are.

If a company collects data about household situation and income of their customers, for statistical analysis and maybe some data mining and targeted advertising, and I leave the fields for number of children and monthly household income blank, would you really enter both as 0? I do have children. And I do have income. I just refuse to share that information with companies that have no need for this information, and no legal basis for asking me about it.


Fair enough points and I agree.

I also have a story about how I thought that NULLs would help, but in the end didn't matter. This is oversimplifying, but we had multiple managers responsible for multiple totals. I was resposible for loading the SQL tables and made sure that NULLs were entered if any of the manager's totals were missing. (I was proud of my error-catching design.) Then one day one or two of the managers couldn't provide their information. Upper management's solution? Ignore them and proceed anyway. So the code ended up changing the NULLs to zeros in the end.

Now I know that this doesn't really prove anything in a way since at times NULLs are helpful. But it does show that at times, NULLs are nothing but an obstacle.

______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
SQLZealot
SQLZealot
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 28
Thanks for the details, it has so much more meaning now!
Mahesh Bote
Mahesh Bote
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 Visits: 1274
vk-kirov (12/8/2010)
Good question.

But this is not an explanation at all:
Explanation: If a subquery returns any null value the main query will not return any rows


I suggest the following explanation.

The SELECT query can be modified as follows:
select *
from dbo.Employees
where EmployeeID not in (SELECT NullableColumn FROM NullOperation)


-->
select *
from dbo.Employees
where EmployeeID not in (NULL, 0, 1)


-->
select *
from dbo.Employees
where (EmployeeID <> NULL) and (EmployeeID <> 0) and (EmployeeID <> 1)



The result of the comparison "EmployeeID <> NULL" is undefined for all values of EmployeeID (unless you set the ANSI_NULLS option to OFF). So the result of the WHERE clause is undefined (i.e. not true), and no rows are returned.


or even this way as well...

select * from dbo.Employees where EmployeeID not in 
(SELECT NullableColumn FROM NullOperation where NullableColumn is not Null)
go



MH-09-AM-8694
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search