Filtered Index – Database Performance

SQL Server 2012 includes filtered indexes and is a great feature for Business Intelligence and Reporting databases.  Filtered indexes allow you to reduce the overhead of having a index on a tables and increase the performance of the query optimizer by already reducing the number of rows that need to be searched.

Using AdventureWorks2012 here is an example of the TSQL to create the filter on the EndDate column in the BillofMaterials table.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate

ON Production.BillOfMaterials (ComponentID, StartDate)

WHERE EndDate IS NOT NULL ;

This index will only include rows that have a non null value in the EndDate column.

There are three advantages to using a Filtered Index over full-table indexes.

• Improved query performance
• Reduced index maintenance
• Reduced index storage

For more complete information on Filtered Indexes see Technet:

http://technet.microsoft.com/en-us/library/cc280372.aspx

The post SQL Server Filtered Index Performance appeared first on Derek Wilson - Blog .