October 22, 2007 at 2:03 pm
if you have a table with millions of records, and you have around 15 columns to look over...
what is the best way to find nulls??
i'm using the traditional: where [MyColumn] is null
is there another way i don't know about?
_________________________
October 22, 2007 at 3:22 pm
To the best of my knowledge that really is the best way to do it. If the columns are indexed that should go fairly quickly though.
Kenneth Fisher
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
October 22, 2007 at 7:35 pm
is there a question of = null instead of is null ??
i'm reading about the 2, and there seems to be some confusion.
just wondering.
for the most part i'm using is null. seems to be working ok.
_________________________
October 22, 2007 at 9:58 pm
WHERE = NULL will not work unless you make a pretty uncommon (some consider it "dangerous") change to the ANSI NULL settings of the server. NULL is not "nothing" and you cannot compare to it. You must use ISNULL as already suggested. It's one of the powers of NULL expecially in Aggragate queries.
And, just in case... NULL should never be treated as a value of any kind... it should always be treated as an absence of data only. Again, it's doesn't mean "nothing", it means "unknown" or "no data" including the value "nothing". 🙂
--Jeff Moden
October 24, 2007 at 3:26 am
I agree.
Null is an very interesting data type.
a=null
b=null
if a=b then 'Yes'
Else
'No'
You will expect to get No since you cannot compare two null values.
October 24, 2007 at 3:30 am
October 24, 2007 at 4:18 am
Excellent! Thanks for all the good feedback. I'm going over some docs now about it.
_________________________
October 24, 2007 at 8:44 am
rockzcm (10/24/2007)
I agree.Null is an very interesting data type.
a=null
b=null
if a=b then 'Yes'
Else
'No'
You will expect to get No since you cannot compare two null values.
Even more interesting... the code you have above isn't T-SQL and doesn't work in SQL Server... 😉
--Jeff Moden
October 24, 2007 at 8:50 am
Pseudo code Jeff, Pseudo code.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
October 24, 2007 at 9:13 am
Ah... got it... Looked more like VBS. Not sure why folks don't just write real code... doesn't take any longer 😛
DECLARE @a INT --Defaults to NULL on creation
DECLARE @b-2 INT --Defaults to NULL on creation
PRINT 'Yes'
ELSE
PRINT 'No'
And, I agree... shows how odd NULLs are... demonstrates that NULLs truly cannot be compared even to each other and that even NULL is not equal to NULL because NULL indicates "unknown"... brings up the old saying that "Null is not nothing".
--Jeff Moden
October 24, 2007 at 9:29 am
...especially when you factor in that although Null is never equal to NULL, NULLs get aggregated as one data point (as that recent featured article pointed out). Kind of an interesting design choice when you compare it to all of the other handlings of NULL.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 24, 2007 at 9:41 am
I always thought the decision to agragate on NULLs made perfect sense. I can't think of the number of times I have needed a count of how many times a field was null.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
October 24, 2007 at 9:58 am
Nulls. Bane of my life. Especially when you have to do JOINs in a SELECT...
I don't understand them apart from they represent a "unknown" value. It's definetely more than trinary and one day I hope they will be outlawed...
Programmers don't understand them. But in many programming languages a NULL has a distinct value.. e.g. C#, where it is very usefult and can be tested
October 24, 2007 at 10:28 am
Kenneth Fisher (10/24/2007)
I always thought the decision to agragate on NULLs made perfect sense. I can't think of the number of times I have needed a count of how many times a field was null.
No doubt it's practical..Just kind of went against their "Null is never equal to Null" mantra they apply. Of course - I suppose the converse is true as well ("Null is never not equal to Null"), so it's a choice to make. It's just kind of an interesting world to be able to quantify your unknowns....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 24, 2007 at 10:47 am
I don't know. I am a programmer and never had an issue with understanding the logic of the knows and unknowns.
Best way I ever could think of to describe is a magician.
He takes two pennies and state he has only two pennies (let's call these bit datatype objects for fun) and places them in his hat on the table. He then does some movement and shows you his left hand with a penny in it (as a bit we will call this TRUE). He then shows you his right hand with no penny in it (as a bit we'll call this hand FALSE). Now he asks if the other penny is in his hat still. You might say yes (TRUE) as an educated guess based on the principal of the provided data but this is a magician, they could just show you no (FALSE) and then ask where has the penny gone. Well the fact is your are unsure so your answer is you don't know (we will call this NULL).
Now he might trick you say to you it is in one of his jacket pockets then ask if the other penny is in his left or right jacket pocket (this hopefully will expalin why NULL does not equal NULL), in both cases this you say I don't know, but you assume one or the other since he asked about both. The fact here is the penny exists somewhere but that existance may not be the same for both scenarios thus your I don't know although to you means you don't know the application of logic to the pockets means it could be in either one or the other. Thus that in itself tells you the left pockets outcome does not equal right pockets outcome as only one penny can exist. But with a magician they could both have a penny or both be empty even. Due to this fact they may or may not be the same result means you cannot evaluate the two as equal without more information and thus NULL can not definitively equal NULL until it becomes known, there is a large degree of uncertainty with evaulating a magicians penny location.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply