asked
Are there any functions that are SARGable?
I have this little beastie in a WHERE clause I'm trying to improve (names changed to protect the innocent): and COALESCE(tbl1.datetime1, tbl1.datetime2, tbl2.datetime3,@end_dttm) >= @start_dttm and it got me to wondering whether it's a blanket rule that ALL functions in the where clause can't be used by the query analyser, or a subset of functions that can't be used?
I believe till now, whenever you will use a function on a column in WHERE/JOIN clause, you will lose the SARGability. As the function logic has to be applied on the column before the comparison, the statistics and index seek are not applicable. Hence, it always result in an index scan or table scan. In your case, most probably a table scan is expected. If I were you, I would have tried to use a staging temp table to evade such comparison OR would have tried to TWEAK the logic according to the requirements.
Interesting discussion about this very topic [here][1] [1]:
http://www.sqlservercentral.com/Forums/Topic1226743-203-1.aspx
Believe me, I was about to enter that discussion as well. But then thought, some other big guns should be responding if that is the case (And they did). I was always thinking of I must be missing something :)
