Today I want to return to a topic I tend to cover annually, because we are always getting new readers, often new to database techniques. The topic is indexing. In fact, I want to talk about a specific method of indexing found in SQL Server, where the index has a where clause, known as a filtered index.
A filtered index does not index the entire contents of a table. It has a where clause restricting the rows included in the index. Here is how it works. Let’s say you have a table with 1 million rows. You create a filtered index on the table resulting in only 10 thousand of those million rows. The net result is that:
- When you modify data in the table, only 1 percent of the records require updating the index as well.
- When you query the table, the index has great performance if the selected data falls within the filter defined by the index creation. This is because the index has far fewer nodes to traverse.
- The filtered index requires less physical space, and may be de-fragmented (re-organized), using less resources.
Filtered indexes are useful if you have large tables where a large percentage of the records frequently queried fall within a narrow range that may be defined in a simple where clause. For more information on how to create filtered indexes, and how they work, see the 2016 SQL Server documentation at https://msdn.microsoft.com/en-us/library/cc280372.aspx.
Cheers,
Ben