February 26, 2010 at 6:42 am
I think the typo is quite obvious.
Thanks to the author: the question was very useful to me, due to the question I learned the new word 'sargable' 🙂
February 26, 2010 at 6:46 am
I got this right because I read the question quickly and didn't notice the missing columns.
However, I have gotten these questions wrong before where there were deliberate 'mistakes' like this.
If this question stood on it's own then it is just a typo. Some questions rely on you spotting or not spotting invalid SQL so the assumption of a typo is dangerous (if losing a point is considered a danger).
February 26, 2010 at 6:47 am
no, no, no, i should not have to guess or assume what the question 'should' say.
🙂
Martin
February 26, 2010 at 7:57 am
Dietmar Weickert (2/26/2010)
- No info about indices on the table/view to be queried? Who could potentially know about SARGable operators. And even if there were this information, who is able to predict the optimizers way to treat a query.
To answer the first question, a SARGable operation is not necessarily one that is working on an indexed column. A SARGable operation is one that would provide a better result if the column were indexed. So you don't need to know if there is an index to know it's a SARGable operation.
To answer the second question, I'd say anyone whose spent a few hours researching performance tuning in SQL server would have a pretty good idea. That's the whole point of SARGable operations -- they've been identified by people who know how the optimizer works as ones which generally enable the query optimizer to use an index to improve performance. You identify them first, and then you know where indexes would help (or not, as the case may be.)
February 26, 2010 at 8:29 am
The answers have been edited to include the column and "none of the above" has been changed. I will award back points. My apologies as I didn't notice the missing columns.
February 26, 2010 at 8:57 am
Tao Klerks (2/26/2010)
Hmm, I was more bothered by the fact that NOT IN() is assumed to NOT be SARGable... In SQL 2000 this was always true when there was more than one value in the NOT IN() parentheses, but as of SQL 2005 the optimizer does a pretty good job of using indexes even then!
At the place where I work we have a weekly Lunch and Learn sessions. About 6 months ago I was giving a presentation about indexes which included a dissection of the SARGable predicates based on the original article by Brad McGehee (the one which is included as a hyperlink in this QoD answer section). Well, when the NOT IN () was investigated when appied to one one the tables in one of our SQL Server 2005 databases, I could not demonstrate that NOT IN () is not SARGable because the execution plan had shown that the respective index was still used. Fortunately, I still had the old SQL Server 2000 database on one of my boxes at home, so I could later confirm the validity of the NOT IN() is not SARGable argument.
On the unrelated note, it looks like occasionally it pays off to come to work late, after Mr. Jones already edited the QoD :-). Since I am very familiar with Brad McGehee's article on SARGable predicates, I did not have any problems answering th QoD. Original typo aside, this is a very good question. Thank you Agrawal.
Oleg
February 26, 2010 at 11:02 am
Aside from the typo, this was a good one. It introduces the concept simply and provides a good resource for further understanding. Although I'd learned long ago that one should avoid functions in the WHERE clause, as with: Where left(column, 1) = 'R', it's good to see how other constructions will or will not fall into the SARGable category.
February 26, 2010 at 11:19 am
I misremembered what SARGable meant and so I got lucky in my answer. A good reminder question.
--
Kevin C.
February 28, 2010 at 2:25 pm
john.arnott (2/26/2010)
Aside from the typo, this was a good one. It introduces the concept simply and provides a good resource for further understanding. Although I'd learned long ago that one should avoid functions in the WHERE clause, as with: Where left(column, 1) = 'R', it's good to see how other constructions will or will not fall into the SARGable category.
In my view the use of LEFT should not prevent an index seek and that it does is a bug in the optimiser; after all, col LIKE 'this%' is sargable, so the logically identical LEFT(col,4) = 'this' should also be sargable: having to know which way to write a particular predicate to get the optimiser to notice that an index can be used is really annoying (and a waste of space in every developer's or dba's memory). The current situation is no less stupid than it would be if col < 3 were sargable but 3 > col were not.
I agree it was a fair query - but I looked late and never saw the typo.
Tom
February 28, 2010 at 11:33 pm
Tom.Thomson (2/28/2010)
john.arnott (2/26/2010)
Aside from the typo, this was a good one. It introduces the concept simply and provides a good resource for further understanding. Although I'd learned long ago that one should avoid functions in the WHERE clause, as with: Where left(column, 1) = 'R', it's good to see how other constructions will or will not fall into the SARGable category.In my view the use of LEFT should not prevent an index seek and that it does is a bug in the optimiser; after all, col LIKE 'this%' is sargable, so the logically identical LEFT(col,4) = 'this' should also be sargable: having to know which way to write a particular predicate to get the optimiser to notice that an index can be used is really annoying (and a waste of space in every developer's or dba's memory). The current situation is no less stupid than it would be if col < 3 were sargable but 3 > col were not.
I agree it was a fair query - but I looked late and never saw the typo.
Tom, I would tend to agree with your disappointment in the optimizer not recognizing the LEFT(col,4) ='xxxx' construction as equivalent to col LIKE 'xxxx%'. For what it's worth, I just confirmed on a SQL 2005 test db I already had that the estimated plan for a LIKE specifies an index seek where a logically equivalent LEFT() resolves to an index scan. If/Until MS decides to address this, I suppose we'd better continue to pay attention to our predicate constructions at a fairly low level.
------
edit to clarify I'm on SQL 2005.
March 4, 2010 at 6:11 am
Steve Jones - Editor (2/26/2010)
The answers have been edited to include the column and "none of the above" has been changed. I will award back points. My apologies as I didn't notice the missing columns.
Hi Steve, I did not get back my points so far...
Best regards,
Dietmar Weickert.
March 30, 2010 at 8:30 am
Dietmar Weickert (2/26/2010)
Once again a crap QotD.- A missing column name a typo? Hard to believe.
- No info about indices on the table/view to be queried? Who could potentially know about SARGable operators. And even if there were this information, who is able to predict the optimizers way to treat a query.
Just choose whatever you want. It may be right or wrong anyways. Leaves a QotD without value for the community.
Anyone else bugged by a persistent whining noise? :laugh:
March 30, 2010 at 8:32 am
Bit hash calling the lack of an automatic transformation from LEFT to LIKE a bug!
Are you sure it is a provably safe transformation in absolutely all cases?
March 22, 2012 at 11:43 am
Oh, I adore questions with fast retrieval of rows. Thank you
Igor Micev,My blog: www.igormicev.com
March 23, 2012 at 7:18 am
SQL Kiwi (3/30/2010)
Bit hash calling the lack of an automatic transformation from LEFT to LIKE a bug!Are you sure it is a provably safe transformation in absolutely all cases?
I thought I'd replied long ago, but noticing a new post here I saw I hadn't. better late than never, I suppose.
The optimiser would have to look at each case and decide whether the transformation was allowable; and if it was allowable, is it worth doing in the particular case (the latter is the same as for any other choice the optimiser makes, of course).
The transformation is from LEFT(Col,L)=S to Col LIKE tr(left(S,L))+'%', where the expression tr(left(S,L))+'%' is calculated once only (by the optimiser); tr is the string escaping needed to deal with wildcards occurring within left(S,L) - they must not be treated as wildcards because they represent themselves; the transformation is permissable provided that (i) left(S,L) does not have a space as its final character and (ii) S and L are constants known to the optimiser, and will be the same each time the generated plan is used. If any of these conditions fails, the optimiser must not male the transformation.
Of course there are many other "bugs" that probably have higher priority than this ("bug" of course means "possibility for improvement in teh optimiser", not anything else).
Tom
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply