What is Filtered Indexes in Sql Server 2008

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

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/