Indexing for Different Queries
When you have a table that is queried using many different kinds of criteria it is difficult, and sometimes impossible, to build a reasonable number of indexes supporting all the different queries. Most developers know that as you create more indexes on a table it slows down the performance of the table for creating or updating records.
What options are available to you when you face this kind of situation? There are many different options. Today I want to talk about the option of using SQL Server’s capability to utilize two or more indexes to support your query.
As you may know, an index in SQL Server is stored in a manner much like a table. In fact, it looks like a table with one virtual column which is a pointer to the location where the data from the base table resides (this only applies to non-clustered indexes). So, if you create an index on a single column, that index essentially contains two attributes; the indexed column, and an internal pointer to the location of the data in the original table.
If a query may be optimized by using two different indexes, it will perform a join of the two different indexes finding the intersection of those records meeting the requested criteria prior to joining to the original data table. So, in those cases where you have say ½ dozen different columns that may be used in different combinations to fulfill users queries, it may be more efficient to create an index on each column independently, and SQL Server will handle the different combinations with the query plan, just as it does when joining tables.
The down side of using smaller indexes is the extra effort required to locate the intersection of the individual indexes. In my experience, the more unique the values are for the columns included in the index, the better the query performs. Indexes with low data distribution on large tables provide a lesser benefit.
Have you used small indexes in this fashion? Share your thoughts on this technique below, or drop me an email at btaylor@sswug.org.
Cheers,
Ben