January 4, 2011 at 4:12 am
Hi ALl
A very simple question for which i do not have the correct and logical answer.
say , i have 2 queries like
1.select * from adventureworks
where id=@id or id is null
2.select * from adventureworks
where id=@id or @id is null
For better performance where should these null checks be done ?
January 4, 2011 at 4:23 am
Those two queries are not equivalent.
select * from adventureworks
where id=@id or id is null
This will return any rows from the table where ID column is equal to the value of @ID or where the ID column is null
select * from adventureworks
where id=@id or @id is null
If @ID is not null, this will return all rows where ID column is equal to the value of @ID. IF @id is null, it will return all rows from the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2011 at 6:40 am
What is your actual requirement?
January 5, 2011 at 3:24 am
As Specified by the Gila,,the two queries won't fetch the same results,,,
In general checking for nullablilty for parameter would be better than checking for nullablilty for the column.
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
January 5, 2011 at 4:05 am
parm_singla (1/5/2011)
In general checking for nullablilty for parameter would be better than checking for nullablilty for the column.
Errr... Depends completely on what you're trying to do. If you need to check for null values in the column, then you need to check the nullability of the column.
Asking whether it's better to check the column or the parameter is like asking which is better, watermelon or tuna.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2011 at 6:17 am
GilaMonster (1/5/2011)
Asking whether it's better to check the column or the parameter is like asking which is better, watermelon or tuna.
Watermelon. Definately. 😎
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 5, 2011 at 1:30 pm
what is the difference between Watermellon and Tuna I ask you???
On the one hand they are both edible items, originating from life forms and bring forth nourishment. On the other hand Watermellon is easier to catch and clean than Tuna and tastes better to me.
This is a classic relational conundrum which demands the highest introspection and study....
The probability of survival is inversely proportional to the angle of arrival.
January 5, 2011 at 2:10 pm
Without a doubt they both taste better raw than cooked.
_______________________________________________________________
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/
January 5, 2011 at 2:38 pm
GilaMonster (1/5/2011)
parm_singla (1/5/2011)
In general checking for nullablilty for parameter would be better than checking for nullablilty for the column.Errr... Depends completely on what you're trying to do. If you need to check for null values in the column, then you need to check the nullability of the column.
Asking whether it's better to check the column or the parameter is like asking which is better, watermelon or tuna.
This seems like a seriously difficult question. I suggest we all get together and extensively test the two food items to see if we can generate an authoritative consensus.
Also, what are the full boundaries of the question? Are we comparing, say, watermelon icecream to tuna caserole? How about the qualities of each after the taste buds have been whipped into submission by one's alcoholic drink preference?
I say we all go to Roy's house and see what we can come up with for full, thorough testing. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2011 at 3:01 pm
You lot are nuts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2011 at 3:04 pm
Yes, yes we are. On that note, if we are going to Roy's house let's just skip the watermelon and casserole and head straight for copious amounts of liquor. 😛
_______________________________________________________________
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/
January 5, 2011 at 3:09 pm
GilaMonster (1/5/2011)
You lot are nuts.
Now wait a second... I resemble that remark.
I think GSquared has a valid point. How *can* we determine the validity of a comparison between Watermellons or Tunafish without a small libation to.. to... shall we say... lubricate the pathway?
I rest my case.
The probability of survival is inversely proportional to the angle of arrival.
January 6, 2011 at 7:58 am
GilaMonster (1/5/2011)
You lot are nuts.
To misquote Obelix: These DBAs are crazy! 😀
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2011 at 8:00 am
GSquared (1/6/2011)
GilaMonster (1/5/2011)
You lot are nuts.To misquote Obelix: These DBAs are crazy! 😀
😀 In my native language, it would be translated as "Weird guys, those DBA's."
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 6, 2011 at 8:39 am
Koen (da-zero) (1/6/2011)
GSquared (1/6/2011)
GilaMonster (1/5/2011)
You lot are nuts.To misquote Obelix: These DBAs are crazy! 😀
😀 In my native language, it would be translated as "Weird guys, those DBA's."
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. 🙂
(Appologies to the OP for hijacking this thread. Doesn't mean I'll stop doing so, but I do appologize for it.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply