Basics of Filtered Indexes and Filtered Statistics in SQL Server
Starting with SQL Server 2008, Microsoft has introduced Filtered Indexes and Filtered Statistics, where in you can provide a where condition while creating the index or statistics, thus limiting the amount of rows in an index which is useful is some cases where only a subset of the data has to be read from, which greatly improves the performance.
What are Filtered Indexes?
A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes. A filtered index provides Improved query performance and plan quality, Reduced index maintenance costs, Reduced index storage costs.
What are Filtered Statistics in SQL Server?
SQL Server optimizer uses statistics to create suitable execution plan. So, it is important to keep the statistics up to date for in SQL Server database. SQL Server automatically manages the statistics, but sometime DBAs has to setup jobs to perform statistics update for better performance when there are frequent changes to the data in the tables. Starting with SQL Server 2008, Microsoft has introduced filtered statistics to further benefit the performance of the queries against large tables. Below are some of the basics of the Filtered Statistics in SQL Server.
As per official Microsoft documentation, “Filtered statistics can improve query performance for queries that select from well-defined subsets of data. Filtered statistics use a filter predicate in the WHERE clause to select the subset of data that is included in the statistics.”
It is very easy to create the filtered indexes or statistics which can be create using Create Index or Create Statistics and specifying a where clause with a condition that limits the number of rows matching the requirement. Once the index or statistic is created, SQL Server can use it to satisfy the query thus performing better.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings