Sunday, May 22, 2016

SQL - Difference between Filtered Index and Indexed View

We can index across a view containing multiple tables for a start and the where and case clauses can be very sophisticated. This sophistication comes at a price as the index can be larger and is will be updated during an insert whereas a filtered index will only be updated during insert if the new row meets the where clause in the filter. Indexed Views do not support non-unique clustered indexes and the first index on a view have to be clustered because only a clustered index can enforce uniqueness and store the rows at the same time.
What does mean of Filtered Index?
A Filtered Index is an optimized non-clustered index which allows us to define a filter predicate with WHERE clause whenever creating the index. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
In very simple words, we can say that “Filtered indexes allow us to create an index on a subset of data using a filtering predicate”. 

What does mean of Indexed view?
An Indexed View is the most powerful feature of SQL which was introduced with SQL Server 2000. The main purpose of the indexed view is to run the queries faster and improve the performance of the application. Indexed view has a unique clustered index nature and let us pre-computes all sorts of joins, aggregations, and computations because we can’t use these conditions into every query. 
What are differences between Filtered Index and Indexed View?
Filtered indexes have the following advantages over indexed views-
Criteria
Filtered Index
Indexed Views
Maintenance Costs
Reduced index maintenance costs because the query processor uses fewer CPU resources to update a filtered index
The query processor uses more CPU resources to update a Indexed View.
Plan Quality
Improved plan quality because during query compilation, the query optimizer considers using a filtered index in more situations
They are not so benificial in the query optimizer as Filtered Index
Online index rebuilds
A Filtered indexes while they are available for queries
Online index rebuilds are not supported for indexed views
Non-unique indexes
Filtered indexes can be non-unique
Indexed views must be unique
Only One Table
A Filtered Index is created on column(s) of a particular table.
Index Views can be created on column(s) from multiple base tables.
Simple WHERE criteria
A Filtered Index can not use complex logic in its WHERE clause, for example the LIKE clause, NOT IN, OR and dynamic / non-deterministic predicates like WHERE col >= DATEADD(DAY, -1, GETDATE()) are not allowed, only simple comparison operators are allowed.
This limitation does not apply to indexed views and you can design your criteria as complex as you want.

Type of Indexes in SQL
References:

https://msdn.microsoft.com/en-IN/library/ms191432(v=sql.90).aspx

No comments:

Post a Comment