Covering Index vs. Indexed View
A covering index and an Indexed View are two different techniques for solving the same problem; performance needed when retrieving data from an table or view that has too much overhead for retrieving a subset of the possible data.
Both techniques operate as a virtual table if they contain the necessary data required by queries. As demonstrated some weeks back, a covering index is an index created on a single table, or indexed view, and having all necessary columns in either the index key column list or the index include column list.
An indexed view is a view that has been created against one or more tables using Schema Binding. By including schema binding in the view definition, it may be have a unique clustered index created against the view definition, thus converting the view from a virtual object to an object materialized physically on the disk in the form of the index. Once a view has a unique clustered index, additional indexes may be created against the view; including covering indexes resulting in both techniques being implemented simultaneously.
I generally use an Indexed View only in situations where I need good performance against a query consisting of a number of tables. If a query may be fulfilled from a single table, a covering index is a good strategy.
Be aware of the performance cost associated with creating covering indexes and indexed views. Just as every index created on a table must be maintained as data is manipulated, the same is true of an indexed view. The key difference being that the materialized view is impacted by each table making up the view definition. Indexed views may improve your query performance, but they can kill data modification performance.
Some things to consider:
- If the table performs reasonably without a covering index then consider holding off.
- If a covering index is warranted, consider creating the index on demand, and dropping it if the need for the index is not consistent. For example, I often create and then drop indexes during nightly batch processing or ETL processes.
- If a covering index is mandatory at all times, and performance is impacted to highly on maintenance of the underlying tables, consider creating a denormalized data mart. Maintain the mart in a process that is near real time, but does not slow down the management of the normalized data.
If you are interested in more information about covering indexes and indexed views, you will find plenty of help by running a search on those two terms.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Troubleshooting SQL Server 2008 Locking (Part 1)
In this article, Alexander Chigrik explains some problems that you can have with SQL Server 2008 locking. He also tells how you can resolve these problems.
Featured White Paper(s)
How to Implement an Effective SharePoint Governance Plan
Written by AvePoint
Moving past the "what" and "why" of governance, an even… (read more)