What is Filtered Indexes in Sql Server 2008

Question

Hi What is Filtered Indexes in SQL Server 2008. Explain its benefits and give an example ?

Satyapriya Nayak · Answer

Hi Arun Please refer the below links http://www.techrepublic.com/blog/datacenter/filtered-indexes-in-sql-server-2008/490 http://www.mssqltips.com/sqlservertip/1785/sql-server-filtered-indexes-what-they-are-how-to-use-and-performance-advantages/ http://www.databasejournal.com/features/mssql/article.php/3811161/Exploring-SQL-Server-2008146s-Filtered-Indexes.htm Thanks

Kunal Vaishya · Answer

SQL SERVER – 2008 – Introduction to Filtered Index – Improve performance with Filtered Index Filtered Index is a new feature in SQL SERVER 2008. Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance reduce index maintenance costs and reduce index storage costs compared with full-table indexes. When we see an Index created with some WHERE clause then that is actually a FILTERED INDEX. For Example If we want to get the Employees whose Title is Marketing Manager for that let's create an INDEX on EmployeeID whose Title is Marketing Manager and then write the SQL Statement to retrieve Employees who are Marketing Manager. CREATE NONCLUSTERED INDEX NCI_Department ON HumanResources.Employee ( EmployeeID ) WHERE Title = 'Marketing Manager' Points to remember when creating Filtered Index: - They can be created only as Nonclustered Index - They can be used on Views only if they are persisted views. - They cannot be created on full-text Indexes. Let us write simple SELECT statement on the table where we created Filtered Index. SELECT he.EmployeeID  he.LoginID  he.Title FROM HumanResources.Employee he WHERE he.Title = 'Marketing Manager' More References refer this link http://blog.sqlauthority.com/2008/09/01/sql-server-2008-introduction-to-filtered-index-improve-performance-with-filtered-index/