December 9, 2010 at 1:29 pm
Artur Komkov (12/9/2010)
What's why I always try to avoid nullable columns or make something like that:select * from dbo.Employees where EmployeeID
not in (SELECT isnull(NullableColumn,0) FROM NullOperation)
Artur should get a gold star and extra points for using the function that was created to keep the ANSI NULL issue the question points out from occuring. I work on a database application that has 3000+ stored procedures. Almost all of them use the isnull function.
December 9, 2010 at 5:22 pm
Artur Komkov (12/9/2010)
What's why I always try to avoid nullable columns or make something like that:select * from dbo.Employees where EmployeeID
not in (SELECT isnull(NullableColumn,0) FROM NullOperation)
While that would indeed avoid this issue, it also makes it a lot harder for the optimizer to use an index (if any) on the NullableColumn. Here is an alternative that will still use indexes:
SELECT * -- Use column list instead!
FROM dbo.Employees AS e
WHERE NOT EXISTS
(SELECT *
FROM NullOperation AS n
WHERE n.NullableColumn = e.EmployeeID);
December 10, 2010 at 2:40 am
The code returns an error:
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'dbo.NullOperation', because it does not exist or you do not have permission.
The table is created using default schema which the contributor has assumed to be dbo (evident in the 'drop table dbo.NullOperation' statement). My default schema is not dbo!
Having said that, I still like the question because it has proven its point - the error occurred AFTER the empty recordset was returned by the select statement.
December 10, 2010 at 6:16 am
I have tried to avoid NOT IN clauses, I couldn't remember why but here it is.
I usually use outer joins eg
select * from #Employees e
left outer join #NullOperation n
on e.EmployeeID = n.NullableColumn
where n.NullableColumn IS NULL
so that I get what I expect, which is what we all want!
December 10, 2010 at 7:31 am
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... 😎
December 12, 2010 at 6:29 am
Good question with clear answer options, but as several have pointed out the explanation is not a real explanation.
Tom
December 13, 2010 at 2:15 am
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.
December 14, 2010 at 10:31 am
Thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 14, 2010 at 11:10 am
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.
December 14, 2010 at 12:23 pm
Good question - thanks! NULLs can be hazardous, they should come with a warning.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
December 14, 2010 at 7:23 pm
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)
December 15, 2010 at 6:19 am
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.
December 15, 2010 at 8:22 am
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
December 15, 2010 at 3:27 pm
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.
December 15, 2010 at 3:41 pm
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
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply