Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

Nulls Expand / Collapse
Author
Message
Posted Tuesday, December 14, 2010 7:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 6:19 AM
Points: 262, Visits: 919
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)
Post #1034855
Posted Wednesday, December 15, 2010 6:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 5, 2013 7:19 AM
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...


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.
Post #1035071
Posted Wednesday, December 15, 2010 8:22 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:46 PM
Points: 598, Visits: 3,821
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
Post #1035180
Posted Wednesday, December 15, 2010 3:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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
Post #1035528
Posted Wednesday, December 15, 2010 3:41 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:46 PM
Points: 598, Visits: 3,821
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
Post #1035541
Posted Wednesday, December 15, 2010 3:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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
Post #1035547
Posted Wednesday, December 15, 2010 4:07 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:46 PM
Points: 598, Visits: 3,821
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
Post #1035550
Posted Sunday, December 26, 2010 5:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 10:45 AM
Points: 39, Visits: 28
Thanks for the details, it has so much more meaning now!
Post #1039291
Posted Thursday, March 17, 2011 6:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:57 AM
Points: 1,098, Visits: 1,222
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
Post #1079595
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse