Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Using Indexed views in SQL Server 2008

  • 29 November 2009
  • Author: kylewalker
  • Number of views: 12681
  • 0 Comments

Ever since SQL Server 2000, views have been an option as a performance enhancing querying tool.  When adding a clustered or a non-clustered index to a view, especially a particularly large one, the performance benefits can be much more noticeable (Keep in mind, you must have a clustered index on the view before you can put a non-clustered index on it).  Queries that involve computations or aggregations will benefit a great deal from an indexed view because these values are evaluated and materialized when the indexed view is created rather than at run-time.  This, of course, means that the data is physically stored and if these aggregations aren’t needed on a regular basis, you may not want to pay the price in disk space.  You may also want to create a partition-aligned indexed view if the view is for, say a large fact table, which is quite often the case in Data Warehouse scenarios.  Otherwise, the view would have to be dropped and recreated every time a partition is switched.  Below, I will cover the key points to remember when creating an Indexed View for your data. 

There are quite a few requirements that a view must meet before creating a clustered index: 

·      When the CREATE VIEW statement is executed the ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON. 

·      When all of the tables that the view will be referencing were created, the ANSI_NULLS option must have been set to ON. 

·     All tables referenced by the view and the view itself must be in the same database and have the same owner. 

·     The view can only reference base tables (no views allowed). 

·     The view must be created with the SCHEMABINDING option. 

·     User-defined functions referenced in the view must have been created with the SCHEMABINDING option. 

·     Tables and functions must be referenced by two-part names only. 

·     All functions referenced by expressions in the view must be deterministic. 

·     The columns in the SELECT statement in the view must be stated explicitly.  (No “SELECT * FROM” allowed). 

·     Columns can only be selected once as a simple expression.  A column can be selected multiple times only if all but one reference is a part of a complex expression. 

·     The SELECT statement cannot contain a DISTINCT, a TOP clause, COUNT(*), a derived table, a rowset function, UNION operator, subqueries, outer joins, self joins, a TOP clause, or ORDER BY clause. 

·     The SELECT statement cannot contain the following functions:  AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP. 

·     The SELECT statement cannot contain a SUM function on a column that is nullable. 

·     The SELECT statement cannot contain a COMPUTE or COMPUTE BY clause. 

·     If GROUP BY is not specified, the view select list cannot contain aggregate expressions. 

·     If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, CUBE, or ROLLUP. 

·     A column resulting from an expression that either evaluates to a float value or uses float expressions for its evaluation cannot be a key of an index in an indexed view or a table. 

There are some cases where you might want to use a function that is not allowed in an indexed view.  In these cases, it is possible to calculate the desired function by including in the view values derived from other functions to compute the desired function.  For example, if you need and average for a field, you can include in the view a column that sums (SUM(field_name)) a field and another column that counts (COUNT_BIG(field_name)), and then when you go to query that view, you can calculate an average.

The syntax and naming convention is the same for creating an index on a view as it is for a table.  Here is an example:

 

 

Note:  Another thing to consider is that when you have an indexed view on top of a table or joining multiple tables.  Writing new data to or updating the base table will be much more costly now that there is a view that must be maintained as well.  So in the situation I am currently in on my project, where a table is inserted into once a week and as many as 25 million records can be inserted each time, this is a very important thing to keep in mind.  (Thank you for the input Tim!)

Print
Tags:
Rate this article:
4.0

kylewalkerkylewalker

Other posts by kylewalker

Please login or register to post comments.