In response to the editorial on Filtered Indexes, David shares one way he uses them. He says many of his tables have an EffectiveStatus column, and the status often sought has a value of 1. I don’t know the data domain of the other values…nor the percentage of the rows in the table having an EffectiveStatus of 1. Still, this is a great use of a Filtered Index, especially if you use a column like EffectiveStatus to perform soft deletes. A soft delete is where you change the status of the record to deleted rather than physically deleting the record.
One of the issues you may find by having a filtered index on the EffectiveStatus column, using David’s example, is that it only filters that particular property. What happens when you have a query that wants all of the active records, EffectiveStatus = 1, where the StateCode = ‘CA’ (California)? How do we index for that?
- You could create a filtered composite index where you filter on the EffectiveStatus = 1, and then include the StateCode.
- You could create multiple filtered indexes where the EffectiveStatus = 1 and the StateCode = ‘CA’. In the USA, this would require at least 50 unique indexes.
- You could create two separate indexes. First a filtered index on EffectiveStatus = 1. Second, create an index on StateCode.
The third option is the easiest to implement, and given the data distribution (the number of records in the table/index, and the distribution of the values within the index pages) SQL Server will determine when to use the indexes or not.
A good exercise is to create multiple granular indexes (single column index) on a table, build queries using different columns in the where clause, and then view the query plan. Interestingly, SQL Server will often use the indexes as tables to reduce the work load. Recognize that an index always has the key to the record in the clustered index or the heap where the table data is stored. Therefore, any index created on any column of the table will have both the specified column, and the pointer to the record. Because of this, two indexes can be queried based on the filtering criteria, and joined together on the pointer, resulting in the intersection of the two indexes matching the filtering requirements of the query. Now, this intersection result may be joined to the physical table data to retrieve the other columns contained in the table (if they are needed in the query).
It is for this very capability of utilizing multiple indexes in a query that the filtered index demonstrates its usefulness. If you are joining the results of two separate indexes on the pointer, then it performs better if you have less records to process, as is the intention of using a Filtered Index. This increases the flexibility of index utilization when you have indexes covering few or even a single column within a table.
Cheers,
Ben