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.