Creating Indexed Views for Improving performance.
Indexed views are created in two steps
- Creating View
- Creating Indexes on the view
The syntax for creating the indexed view is different from that for creating a normal view.
Advantages Of Indexed Views:
- To Improve the performance of select queries
- Avoid Complex joins at run time and make the joined data readyly available
- Index data on columns on multiple tables
- The data from multiple tables is denormalized and helps in fastening the queries that fetch data
- Joins that are frequently used can be converted into indexed views and thus reducing the query resopnse time of multiple queries
- Advantage of having Higher Disk Space can be converted into having high query performance using Indexed views
Disadvantages Of Indexed Views:
- Slows down the performance of the insert,update,delete statements on the tables used in indexed views
- Increases the disc space used by the database as the views are created with physical data
Refer to the following link for complete list of Pros and Cons of Indexed Views:advantages and disadvantages of indexed views
Hence the decision of creating indexed views can be taken based on the following
- Availability of disc space
- Amount of performance improvement needed
- Number of update,delete,insert quereies fired on the tables used in the indexed views
Please refer to the following MSDN Link to have further details about creating Indexed Views:
MSDN Link