Sargagility != Shagability
If you ever write SQL queries you should know about sargability. Sargable (Search-Argumentable) refers to the ability of a database server to optimize the execution of SQL statements by using indexes. The ability to write sargable predicates (conditions in the where statement) is the crux of a database application that performs well.
In general (and at the risk of oversimplifying the issue), you should strive to write your where conditions in the form FIELD sargable-operand VALUE, where FIELD is some indexed field and VALUE is some constant. Some sargable operands include =,<,>,>=,<= and BETWEEN. When at least some of the conditions are sargable, SQL can use indexes on the table to efficiently find the results; otherwise it has to scan the table for data (which involves reading data pages into memory).
The most common place where programmers get into trouble is dealing with dates, especially when removing the time portion of a date. Specifically, writing a query with an expression on the left side of a condition renders it non-sargable. For example, WHERE CONVERT(char(8), datecol, 112) = '20061010' cannot be optimized because of the CONVERT function on the left. Rewrite the query like this to make it sargable: WHERE datecol >= '20061010' and datecol < '20061011'. Note the < in the second part of the condition.
And how do you know that the query is using an index? Simply showing the execution plan in Query Optimizer and hovering your mouse over the node that executes your query will show any indexes involved in the query plan.
On a humorous note, when researching sargability, google suggested I look for shagability! Well, I guess a query that is sargable is also shagable in my book.
To find out more, this is a really good reference on all aspects of datetime fields in sql server and here is a link for more information on sargable.