Indexes - part 3

In part 2 we have seen the clustered index and nonclustered index and let's continue with another new indexes introduced with Sql server 2005/2008


SQL Server 2005 introduces Index covering

By creating a non-clustered index that contains all the columns used in a SQL query called index covering. So the query accesses only the index file; it doesn't touch the table at all. In fact, there is no need to access the table because all the columns necessary to satisfy the query are already stored in the index. This is index covering. The index is much smaller than the table, which is why the query read many fewer pages.

SQL Server 2008 introduces Filtered indexex

Filtered indexes allow you to create a filter on an index. The index B-Tree will only contain the rows of data that meet the filtering criteria. This allows you to reduce the amount of data contained in an index which means that you are also reducing the data affected in an index by data modifications and also decrease usage of disk space. This often involved partitioning the tables in SQL Server 2005 or archiving data in earlier versions. Ideally we can say that Filtered Index is an optimized non clustered index (Filtered Index can only be created as a nonclustered index on a table), which is best suited for those queries that select a very small percentage of rows from a given table.

Points to remember with Filtered Index:

• They can be used on views only if they are persisted views
• They cannot be created on full-text indexes.