Even if it sounds almost the same as the regular views, indexed views are completely different context. That type of the views are not only about the abstraction but more about performance. When you create the indexed view, SQL Server “materializes” the data in the view into physical table so instead of doing complex joins, aggregates, etc, it can queries the data from that “materialized” table. Obviously it’s faster and more efficient.
When to Use :
Indexed views have both a benefit and a cost. The cost of an indexed view is on the maintenance of the clustered index (and any non-clustered indexes you may choose to add). One must weigh the cost to maintain the index against the benefit of query optimization provided by the index. When the underlying tables are subject to significant inserts, updates, and deletes, be very careful in selecting the indexes (both table and view) that will provide the greatest coverage across your queries for the lowest cost. Typically, environments that are best suited for indexed views are data warehouses, data marts, OLAP databases, and the like. Transactional environments are less suitable for indexed views. Look for repeating joins utilizing the same columns, joins on large tables, aggregations on large tables, and repeating queries as potential candidates for indexed views. Be careful of creating indexed views where the result set contains more rows than the base tables as this will be counterproductive.
Steps to create Index View :
1.Create View Schema binding option :
This means that once the indexed view is created, the underlying tables cannot be altered in any way that would materially affect the indexed view unless the view is first altered or dropped.
2.Create the unique clustered index on the view making it an indexed view .
Once this index is created, the result set of this view is stored in the database just like any other clustered index. Any query that explicitly uses the view will be able to take advantage of the index on the view. Queries that contain a predicate similar to the view and that fall into the range defined by the view may also reap the optimization rewards of having that index available
Even though the query does not use the indexed view, the optimizer has the option of using the clustered index created on the view if it provides better performance than the clustered or non-clustered indexes on the base table. If you want the optimizer to always choose the indexed view over the base tables when optimizing a query containing an index view, you must use the hint
When to Use :
Indexed views have both a benefit and a cost. The cost of an indexed view is on the maintenance of the clustered index (and any non-clustered indexes you may choose to add). One must weigh the cost to maintain the index against the benefit of query optimization provided by the index. When the underlying tables are subject to significant inserts, updates, and deletes, be very careful in selecting the indexes (both table and view) that will provide the greatest coverage across your queries for the lowest cost. Typically, environments that are best suited for indexed views are data warehouses, data marts, OLAP databases, and the like. Transactional environments are less suitable for indexed views. Look for repeating joins utilizing the same columns, joins on large tables, aggregations on large tables, and repeating queries as potential candidates for indexed views. Be careful of creating indexed views where the result set contains more rows than the base tables as this will be counterproductive.
Steps to create Index View :
1.Create View Schema binding option :
This means that once the indexed view is created, the underlying tables cannot be altered in any way that would materially affect the indexed view unless the view is first altered or dropped.
2.Create the unique clustered index on the view making it an indexed view .
Once this index is created, the result set of this view is stored in the database just like any other clustered index. Any query that explicitly uses the view will be able to take advantage of the index on the view. Queries that contain a predicate similar to the view and that fall into the range defined by the view may also reap the optimization rewards of having that index available
Even though the query does not use the indexed view, the optimizer has the option of using the clustered index created on the view if it provides better performance than the clustered or non-clustered indexes on the base table. If you want the optimizer to always choose the indexed view over the base tables when optimizing a query containing an index view, you must use the hint
NOEXPAND.
No comments:
Post a Comment