There is a lot of red tape involved in creating indexed views – but the bottom line is they are worth it, particularly for reporting.
If you have ever created Indexed Views before you most definately would have come across an error or two throughout the process. Before you put this into the too hard basket consider the following scenarios where the benefits of fast query performance will be realised…
Applications that benefit from the implementation of indexed views include:
- Joins and aggregations of large tables
- Repeated patterns of queries
- Repeated aggregations on the same or overlapping sets of columns
- Repeated joins of the same tables on the same keys
- Combinations of the above
Indexed views are therefore likely to benefit in the following scenarios :
- Decision support workloads
- Data marts
- Data warehouses
- Online analytical processing (OLAP) stores and sources
- Data mining workloads
Ok lets look at some basics. The first thing we need to do is create our view. We can then create one or more indexes on our view starting with a clustered index.
To create the view we will use a similar syntax to creating a regular view except we will use the WITH SCHEMA BINDING Option see Schema Binding …
CREATE VIEW dbo.vw_SomeView WITH SCHEMABINDING AS SELECT colKey, col2, col3 FROM dbo.Table1
Note that any tables mentioned in the SELECT statement of your view must be in the two part owner.object name format. There are a heap of caveits that could bring you down at this point which would prevent you from creating the view. One of which is you should not use deterministic functions. E.g. DateAdd returns the same value for a set of parameters each time, whereas getdate() does not i.e don’t use getdate() in your view. CONVERT(datetime, somedate) is non deterministic as it can return different results when different values are provided for the style parameter….consider CONVERT(datetime, somedate,103) as it is deterministic – see Using Convert Within an Indexed View. Note in the example Create View statement above there is explicit specification of columns in the select clause (i.e. no select * …), the select is on a base table not other views and if joins were used, OUTER JOINS would be avoided. The full design considerations for indexed views are outlined here http://msdn.microsoft.com/en-us/library/ms191432.aspx.
Once we have successfully navigated the design guidelines and have successfully created our view, now it is time to create the indexes. We create the index as we would on a table. The first index we need to create on a view is a unique clustered index. We can then create additional none clustered indexes as required.
CREATE UNIQUE CLUSTERED INDEX ci_vw_Table1 ON dbo.Table1 (colKey)
Finally Aaron Bertrand runs through a practical example of using Indexed Views and discusses the good and dark sides of indexed views in this nice example Redundancy is No Evil.