Make more functions SARGable
as Won't Fix
1/21/2010 9:47:37 PM
WHERE cast(SomeDateTime as date) = SomeDate
...is SARGable. The system knows the order doesn't change, and it leverages this in the Query Optimizer.
An index on hierarchyid can take advantage of GetAncestor and other functions.
But as soon as we do:
WHERE datediff(day,0,SomeDateTime) = SomeNumber
...there is no Seek Predicate that can handle this. And yet the order of the rows doesn't change.
WHERE SomeString LIKE 'A%' --is fine, and translated into SomeString >= 'A' AND SomeString < 'B'
WHERE LEFT(SomeString,1) = 'A' --is not.
WHERE SomeNumber + 1 = 3 --is not.
WHERE SomeNumber / 10 = 3 --is not.
WHERE convert(char(6),SomeDate,112) = '200912' --is not.
WHERE convert(char(6),SomeDate,112) + '01' = '20091201' --is not.
...and yet none of these change the order of rows in the index.
These situations (and many more) should be turned into searchable arguments.