On Wednesday I gave a presentation to the St. Louis Visual Basic.Net user group. I really enjoy speaking to this group, they never look like they are falling asleep and we always have a lively discussion. I spoke about indexes, using estimated query plans to troubleshoot queries and the procedure cache.
I brought along a phone book to illustrate several points about indexes. The phone book ended up being a more useful prop than I had originally anticipated. We decided that it had a clustered index on LastName + FirstName, an index on the first letter of the last name, as well as an index on last name.
To prepare for the talk, as well as learn more about how SQL Server decides to use an index myself, I took a look at Kalen Delaney's book "Inside SQL Server 2000". She uses the term "sargable" to describe a WHERE clause when the index will be used. For example, if you have an index over two columns and search on both columns using AND, an index seek will likely be used. That WHERE clause is sargable. This is an example from the AdventureWorks database:
Select
SalesOrderID, SalesOrderDetailID From Sales.SalesOrderDetail where SalesOrderID = 10 AND SalesOrderDetailID = 100If I change the WHERE clause to use OR instead of AND, the WHERE clause is no longer sargable and a clustered index scan is performed:
Select SalesOrderID, SalesOrderDetailID From Sales.SalesOrderDetail where SalesOrderID = 10 OR SalesOrderDetailID = 100
This seems odd until you compare it to finding a name in a phone book. If I am looking for Brad Smith, I look first for Smith and then narrow down the Smiths to find Brad. If I am looking for someone with the last name Smith OR the first name Brad, it is much more difficult. I can quickly find all of the Smiths, but I would have to look at every entry to find all of the people named Brad. So my index is not useful.
