Sql Predicates

I recently found a good article on indxe tuning, but I misplaced the link. Not unusual for me. The article discussed query plans from first principles. I thus will document some of the points I recall from the article and hopefully bookmark the article next time.

For newbies I will give a definition of predicates:-
1. Grammar the part of a sentence in which something is said about the subject
2. Logic something that is asserted about the subject of a proposition

Thus in sql server it normally is part of the where cause: such as where city='Perth'. So when will an index be used to seek? The predicates cannot seek on a single column index if

• the predicate contains a function such as absolute values: where ABS([xDelta]) < 5 =" 0.">
• the predicate contains a Like expression with a wildcard in the first position
  where [city] LIKE '%erth'

In multiple column indexes the order of the keys become important. The predicate on a second column can use an index only if the first column of the index is an equality predicate. Therefore an index will be used when
where [Country]='Australia' and [City] = 'Perth'

However if the predicate is

where [Country] Like '%us%' and [City] = 'Perth' then the index will not be used.