Designing Modular Indexed Views

for RuBoard

Because of the many restrictions on indexed views (no unions, no outer joins, no aggregates other than SUM() and COUNT_BIG(), and so on), you won't find a lot of complex queries that you can cover completely with a single indexed view. However, you will likely find many simpler onesaggregations and joins that your code repeats oftenand this is where the best use of indexed views comes in: modular preprocessing. Indexed views are best suited to simpler queries that take a considerable amount of time to run because of the amount of data they traverse. By materializing the result sets that are produced by these queries, you create the possibility that the optimizer may use an indexed view to avoid having to reproduce a result set that's potentially very expensive. The performance gains can be dramatic.

Indexed views work best when the underlying data doesn't change much. The overhead associated with keeping an indexed view up to date during frequent updates may make them cost more than they're worth. Obviously, this means indexed views probably won't be used much in OLTP environments, but could very well prove quite useful with data warehouses.

Indexed views give SQL Server the ability to create and maintain automatically what people have been building manually for years : static summary and rollup tables. With large amounts of data, it's a common practice to summarize that data into static tables in ways that make it the most useful to the largest number of queries, and use those tables when possible to service requests from the database. It's a common practice, but it requires care and feeding from both the developer and the DBAthe DBA must create and manage the tables over time and the developer must change her app to use them. Indexed views, on the other hand, can be created and left alone. The DBA isn't tasked with making sure they stay up to date. SQL Server takes care of that. On the proper editions of SQL Server, they'll be used automatically if the optimizer thinks they'll reduce the overall cost of a query, so the developer doesn't have to do anything special to take advantage of them. They can't be used in as many situations as summary or rollup tables, but there is overlap between the two strategies. If you are currently creating and maintaining summary tables, you may want to review your physical database design to see whether it could benefit from indexed views.

Indexed View Maintenance

Because the clustered index on a view actually stores the data returned by the view in its leaf nodes, it can become corrupted just like a table can. To check an indexed view for corruption, use DBCC CHECKTABLE. As with tables, DBCC CHECKTABLE can scan an indexed view for corruption and optionally fix it. Likewise, DBCC CLEANTABLE can reclaim space from dropped variable-length columns and text columns in a view as well as a table. DBCC INDEXDEFRAG can defragment indexes on views just as it does table indexes, and DBCC SHOWCONTIG can report fragmentation info for indexed views. Most DBCC commands that operate specifically on tables will also work on indexed views. Check the Books Online for more info .

for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net