is null check in where clause
-
GSquared (1/6/2011)
It's an Asterix comic reference. Obelix says, "These Romans are crazy", based on the Roman "SPQR". See the wikipedia entry on SPQR for details.But your translation works, too. 🙂
I know Asterix for a long time now. I've read the wiki article and apparently the English translation "These Romans are crazy" is the closest to the original French. However, in Dutch it is translated as "Rare jongens, die Romeinen" which is in English "Weird guys, those Romans". Conclusion: the Dutch translator screwed up. Like they do most of the time.
p.s.: sometimes it's pretty funny. In Belgium we have subtitles under the movies. The translation is usually a cleaned-up version of the original text. Hilarious when I was watching Die Hard with a Vengeance
p.p.s: I guess I couldn't go more off-topic than this. But I do not apologize 😎
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen (da-zero) (1/6/2011)
p.s.: sometimes it's pretty funny. In Belgium we have subtitles under the movies. The translation is usually a cleaned-up version of the original text. Hilarious when I was watching Die Hard with a Vengeance
p.p.s: I guess I couldn't go more off-topic than this. But I do not apologize 😎
LOL "Welkom op het feest Pal!"
The probability of survival is inversely proportional to the angle of arrival.
-
After a long time and lots of Tuna,watermelon,liquor,asterix and obelix discussions:
Sorry for not being able to put up the question properly.
what i wanted to ask was :
which one is better among these 2 in terms of performance and best practice:
1.where col1=@colValue or col1 is null
2.where col1 is null or col1=@colValue
Does a null check on the column works faster than a value check even if the column is indexed??
-
I actually don't think either way will make much, if any, difference. You could also write this as
isnull(col1, @value) = @valuehowever I don't find this is as easy to read because you have to stop and look at it._______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
koustav_1982 (1/25/2011)
which one is better among these 2 in terms of performance and best practice:1.where col1=@colValue or col1 is null
2.where col1 is null or col1=@colValue
Those two where clauses (as you've written them) are identical.
As for IS NULL checks on the column vs IS NULL checks on the parameter, the question of relative performance is meaningless because they are different queries with different results intended to do different things. Which one you would use would depend on what results you wanted back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
So is there a better way to do this?
Example...
SELECT *
FROM Table
WHERE Column1 = @Column1
AND Column2 = @Column2 OR @Column2 IS NULL
It seems that if the WHERE statement is not completely Boolean based, then it will work, but is there just a better way to do this?
-
Rich Yarger (6/1/2011)
So is there a better way to do this?Example...
SELECT *
FROM Table
WHERE Column1 = @Column1
AND Column2 = @Column2 OR @Column2 IS NULL
It seems that if the WHERE statement is not completely Boolean based, then it will work, but is there just a better way to do this?
What do you mean better??? I don't understand your comment
if the WHERE statement is not completely Boolean based, then it will work
at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Hey Sean - what I mean is, is there a better way to write the query, than to do it the way I shared? I'm looking into this for a colleague of mine who was really thrown off by it today (he was using the OR operator in the first part of his WHERE clause, and it was throwing off his results. By making it only a part of the 2nd piece of criteria - it works as expected in it's results). So I guess I am wanting to know if I am writing a PROC, and need to use input parameters, is it really necessary for me to qualify it with the @Paramter IS NULL piece?
-
First thing to note is that AND takes precedence over OR. So what you wrote there is evaluated as:
SELECT *FROM Table
WHERE (Column1 = @Column1 AND Column2 = @Column2)
OR @Column2 IS NULL
I suspect what you meant was
SELECT *FROM Table
WHERE Column1 = @Column1 AND
(Column2 = @Column2 OR @Column2 IS NULL)
If so, you have to put the brackets in.
Second...
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gail, thank you so much! That was what I was trying to explain (my apologies for not doing a better job of it). One of my .NET guys was writing some T-SQL earlier, and couldn't get the query to work if he had the OR in the first part of the WHERE Clause. It would work for that first part, but not the second.
We'll give this another go tomorrow when I get in. Thank you again.
P.S. We really missed you at SQLRally Orlando! I was bragging about this ultra cool SQL MVP from South Africa during the Pre-Con, and late night beer and karaoke-fests. I got a lot of Ooooooooooooooooo's and Aaaaaaaaaaaaaaaaaah's! 😀
-
Rich Yarger (6/1/2011)
Gail, thank you so much! That was what I was trying to explain (my apologies for not doing a better job of it). One of my .NET guys was writing some T-SQL earlier, and couldn't get the query to work if he had the OR in the first part of the WHERE Clause. It would work for that first part, but not the second.We'll give this another go tomorrow when I get in. Thank you again.
Just read over that linked post before you go too far down this track...
P.S. We really missed you at SQLRally Orlando! I was bragging about this ultra cool SQL MVP from South Africa during the Pre-Con, and late night beer and karaoke-fests. I got a lot of Ooooooooooooooooo's and Aaaaaaaaaaaaaaaaaah's! 😀
Couldn't afford it. MVP Summit, Immersion training in London and PASS Summit and my finances are a mess. I will be at PASS Summit this year in Seattle. Doing a precon even...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Just one last followup to this thread...
My developer is still running into one additional issue with this logic. In his PROC (as he is testing it out), he is seeing an unusual behavior from the results for one particular column. Here are some snapshots to give you and idea of what he is running into:
Table Definition
Results with NULL
Results with specific value
Now - the werid thing here is that the record you see in the Results with NULL show all the records for the result set. But - if he tries to give the @InvoiceTotal a value, it does not return the result. Is the logic that he has applied the concern, or is there something about the DECIMAL datatype for the column definition that may be the issue? I've run into funky things in the past with precision that exceeded more than 2 spots to the right of the decimal, in the past, and had to go with the NUMERIC data type as a result. Here is the PROC's query...
ALTER PROC [dbo].[uspSearchInvoices]@CustCode varchar(15),
@PONum varchar(30),
@InvoiceNum varchar(30),
@dtFrom datetime,
@dtTo datetime,
@InvoiceTotal decimal,
@status varchar(1)
as
SELECT *
FROM Invoice
WHERE CustID = @CustCode
and (PONumber = @PONum Or @PONum IS NULL)
and (InvoiceID = @InvoiceNum Or @InvoiceNum IS NULL)
and (InvoiceDate > @dtFrom Or @dtFrom IS NULL)
and (InvoiceDate < @dtTo Or @dtTo IS NULL)
and (InvoiceTotal = @InvoiceTotal Or @InvoiceTotal IS NULL)
and (InvoiceStatus = @status Or @status IS NULL)
Thanks again for all of your help!
-
P.S. Gail, your blogs are always awesome! I had him look that over first, but I still think it's something with our logic that is throwing this off. One other note - if he gives a value to some other parameter for the record in my above reply, it will return the result, but not with the @InvoiceTotal param (e.g. the @PONum).
-
Numeric and decimal are the same data type.
What you are doing will NOT perform well, regardless of whether or not you fix the logic problem. That kind of query suffers from terrible performance, unless you're post SP2 and use OPTION (recompile) and then you have compile cost on each execution.
As for the logic error, you're making dangerous assumptions regarding the default scale and precision of decimal. The table explicitly states it, the procedure does not.
Do you know what the default scale and precision of a decimal is? It may not be what you expect...
p.s. Varchar(1)? That's not particularly good. Let's take 3 bytes of space to store a single character...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It's funny you mentioned the RECOMPILE option - I asked him about that, but found out that we are using 2005 and not 2008 (so I'm going to get slammed for this for being in the wrong forum), but if we are to re-factor this query, can you make a best recommendation of a direction I can have him move in to get the input parameters to work as they need to? In all honesty - I was thrown by the need to have the WHERE clause go with both AND and OR for saying IS NULL. I kind of thought that there would be something in the front end of the webapp that would eliminate that worry, but apparently he needs the database to have this flexibility.
P.S. I thought that same thing on the VCHAR(1) column. I'm only trying to help this Developer out, and do not have much say beyond what he is working on now, but I did point that out that if they can make any changes to the schema, that this should be changed.
P.P.S. On the DECIMAL precision question, I only know the static facts as they have been defined. I'm thinking that he just needs to eliminate the IS NULL part of the WHERE clause, and resolve that on the front end instead of in this query. This PROC belongs to a search form on the webapp, so there is no validation - it's just for advanced searching.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply