Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 364

In addition to the disk space required for Index DDL operations, temporary
disk space is required for sorting (one new index at a time), unless the query
optimizer finds the relevant execution plan that does not require sorting.
On the other hand, with the number of rows and column keys, the index will occupy large
storage space, and having an index on every table is not sufficient. To filter the result's null
values will have a heavy workload, and indexes must support the query optimizer to obtain a
well-defined subset of data.
As and when data is inserted, the table size grows, and equally the index management
is essential for query optimization. To manage such large indexes, SQL Server 2008 has
introduced a new enhancement to scalability and performance by introducing the filtered
indexes and statistics feature. In any case, keeping up the statistics on the table will offer a
performance advantage.
Filtered index is an optimized non-clustered index, which means
only a non-clustered index can be a part of a filtered index that
is best suited for the query execution processes, which selects a
small percentage of rows from a given table.
In this recipe, let us work on processes in designing a filtered indexes and statistics feature
that will introduce the ability to create supportive indexes on a subset of rows without having a
need to create an index on a large table.
Getting ready
The key point to designing effective filtered indexes is to understand what query the
application is using and how the query execution relates to subsets of data. As database
designers on the active tables in the databases, we must decide a better indexing strategy by
selecting one column among the key columns as a primary key. By default, SQL Server creates
a clustered index whenever a primary key is chosen.
A best reference for such a subset of data that can be related that have well-defined subsets
are columns with mostly NULL values. For instance, a table Production.Product in
AdventureWorks2008R2 database consists of over 10,000 rows and 40 percent of
data columns with heterogeneous categories of values such as color , reorderpoint ,
listprice , weight , and style with unique characteristics for each product category.
Whereas the frequent queries are executed on a certain category of data, this is where we can
improve the performance of queries for that data by creating a filtered index on the category.