Filtered index (New in SQL server 2008) small and effective.

Ideally we can say that Filtered Index is an optimized non clustered index (Filtered Index can only be created as a non clustered index on a table), which is best suited for those queries that select a very small percentage of rows from a given table. Some of the examples of data which can be well defined subsets could be columns with NULL values or a column which has distinct range of values. Reduction in Filtered Index maintenance cost can be seen when the number of rows in the Filtered Index is very small when compared to a full table index. If the filtered index includes most of the records in the table then it will cost more to maintain a Filtered Index than a Full Table Index. So we needs to be very careful in analysis the best and useful WHERE clause which should be used in when creating a Filtered Index. Filtered Indexes basically can be created on one table and it will improve the simple comparison operation. If your application requires a filter expression which refers to many database tables or has a very complex logic then the best solution is to create a view. In SQL Server 2008 you can create 999 non clustered indexes; however this doesn’t mean that you should create as many non clustered indexes as it will create performance impact when data gets changed often within the tables.