I came across a new (well, to me) term on the MSDN forums today: SARGable, coming from Search ARGuments. Apparently this is DBA slang to describe how well a search term can be used against an index. For example, a constant string is SARGable as it can easily be checked against an index, and LIKE ‘A%’ is SARGable as it is easy to look up anything beginning with A in the index. However, LIKE ‘%A’ is NOT SARGable, as you would need to scan the entire index to find any matches where the column ends in A.
Likewise, if a WHERE clause contains many function calls, such as returning the substring of a datetime column (converted to a varchar) to only return the time portion, the optimiser won’t be able to match this phrase up to an index, and so it is not SARGable.