May 21, 2012 at 3:42 am
I have stored procedures to which I pass strings for selection. Sometimes the string is a single element, sometimes several. I currently have code that parses the text, and subsequently executes a SELECT with either an IN clause or an = clause.
That is,
SELECT ... WHERE fld in ('aaa','bbb','ccc')
or
SELECT ... WHERE fld = 'ddd'
My question is, am I wasting my time with this? Is SQL Server smart enough to realize that a clause with a single element, like
... WHERE fld IN ('ddd')
can be translated to
... WHERE fld = 'ddd'
and will do so without me coding the two instance separately? I made up a few test cases and the execution plan looks identical, regardless of whether I use IN or =, but I'm not that good at reading execution plans yet.
May 21, 2012 at 3:49 am
According to BOL, an IN clause is the same as the different elements with OR constraints.
SELECT ...
WHERE column IN ('a','b','c')
equals to
SELECT ...
WHERE column = 'a' OR column = 'b' OR column = 'c'
If you only have one element, they should indeed be identical.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 21, 2012 at 3:54 am
SELECT ... WHERE fld in ('aaa','bbb','ccc')
The above query(for understanding can be written as):
SELECT ... WHERE fld = 'aaa' OR fld = 'bbb' OR fld = 'ccc'
Which means that Sql Server first gets the list of values inside the IN parenthesis and then checks if "fld" equals to any of those.
That is internally coded by the SQL Server Developers, so we don't have to explicitly write the query as a string of OR Statements and can use the IN clause instead.
So, when there is only one value, like:
SELECT ... WHERE fld in ('aaa')
Then the compiler resolves it and selects the row where "fld" = 'aaa'.
Edit: Oops!!...sorry bout repeating what you said Koen.....we both were typing it at the same time, but you beat me to it. 😀
May 21, 2012 at 3:59 am
According to BOL, an IN clause is the same as the different elements with OR constraints.
Oh? I didn't know that, and I seem to remember reading that multiple ORs are to be avoided when possible. I just looked through BOL, but did not find this about the IN clause. Can you give me a link to where you found this, please?
May 21, 2012 at 4:06 am
pdanes (5/21/2012)
According to BOL, an IN clause is the same as the different elements with OR constraints.
Oh? I didn't know that, and I seem to remember reading that multiple ORs are to be avoided when possible. I just looked through BOL, but did not find this about the IN clause. Can you give me a link to where you found this, please?
Koen must be talking about the First Example from this link.
May 21, 2012 at 4:45 am
vinu512 (5/21/2012)
pdanes (5/21/2012)
According to BOL, an IN clause is the same as the different elements with OR constraints.
Oh? I didn't know that, and I seem to remember reading that multiple ORs are to be avoided when possible. I just looked through BOL, but did not find this about the IN clause. Can you give me a link to where you found this, please?
Koen must be talking about the First Example from this link.
That's right 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 21, 2012 at 5:14 am
Okay, got it. Thank you both, maybe I was thinking about the error 8623 mentioned on that page. Not a problem in my case, there can never be more than about twenty match strings, and usually there will be only one, which is why I was wondering about specifically that case, and whether it made any sense to try 'optimizing' it. According to all this, that's apparently a waste of time, and in any case, the current response time to all the queries using this is pretty much instantaneous.
Appreciate the help.
Pete
May 21, 2012 at 5:40 am
pdanes (5/21/2012)
Okay, got it. Thank you both, maybe I was thinking about the error 8623 mentioned on that page. Not a problem in my case, there can never be more than about twenty match strings, and usually there will be only one, which is why I was wondering about specifically that case, and whether it made any sense to try 'optimizing' it. According to all this, that's apparently a waste of time, and in any case, the current response time to all the queries using this is pretty much instantaneous.Appreciate the help.
Pete
You're welcome. 🙂
May 21, 2012 at 7:33 am
Examining the query plans shows the same result. That was the big hint when I tested this out a few years ago.
When in doubt, check the plan!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply