Indexed Views are Good for Reporting…Inserts please turn away now…

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 …

e.g.


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.

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s