See performance gains by using indexed views in SQL Server

When you use index views in the right situations, they can dramatically improve the performance of SQL Server queries. Tim Chapman explains why performance testing is the best way to tell if indexed views will be beneficial to your database.

A view is a saved T-SQL query in SQL Server. The view definition is stored by SQL Server so that it can be used as a virtual table to simplify queries and add a layer of security to your base tables; however, it does not take up any space in the database. In fact, a view really doesn't do anything until you query it.

Indexed views

In SQL Server 2000 and 2005, you have the ability to add indexes to views. But, if a view is just a stored query definition in the database with no data of its own until it is run, how can you create an index on that definition? Well, it's tricky.

Get SQL tips in your inbox
TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

An indexed view is a view that has been materialized or stored in the database. The index that is created on the view is stored and updated by the database engine as the underlying table is updated. Indexed views work great for situations where your result set returns a lot of rows and needs to be aggregated. However, it is not a good idea to create an indexed view on tables that are highly transactional. This is because the database engine must maintain the index on the view as the base table data is updated, which can degrade transaction performance.

To create an index on a view, the view definition must adhere to a certain set of conditions and session settings, along with the requirement to tie base tables to the view definition. If you determine that your view meets the criteria specifics (which I will discuss later in this article), the first index you must create on your view is a unique clustered one. The first one created must be on a unique set of columns and clustered so that the index is materialized.

Listing A shows an example of how an indexed view is created. The script will create the SalesHistory table and load data into it.

The following code will create a view to summarize the data in the table:

CREATE VIEW vw_salesbyproduct
AS
      SELECT
            Product,
            COUNT_BIG(*) as ProductCount,
            SuM(SalePrice) as TotalSales
      FROM  dbo.SalesHistory
      GROUP BY Product

It doesn't take any time to create the view because it is only a T-SQL query definition. Once the view is created, you can run queries against it like you would a table.

SELECT Product, TotalSales, ProductCount 
FROM vw_SalesByProduct
WHERE product = 'Computer'

If you set the option in either SQL Server Management Studio or Query Analyzer to see the Execution Plan of the query, you will notice that the above query uses a table scan to find the aggregated result based upon the Computer product. Table scans are typically used in situations where there is no index on the data, so the entire result set is scanned through to find the requested results.

With a few simple changes, you can change the view so that you are able to add an index to it to improve search performance. First, you need to make sure the following session settings are set:

SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT OFF
GO

Now you can create our view. To make things easier, I am going to create a brand new view.

CREATE VIEW dbo.vw_SalesByProduct_Indexed
WITH SCHEMABINDING
AS
      SELECT
            Product,
            COUNT_BIG(*) AS ProductCount,
            SUM(ISNULL(SalePrice,0)) AS TotalSales
      FROM dbo.SalesHistory
      GROUP BY Product
GO

The script below creates the index on our view:

CREATE UNIQUE CLUSTERED INDEX
idx_SalesView ON vw_SalesByProduct_Indexed(Product)

To show that an index has been created on the view and that it does take up space in the database, run the following script to find out how many rows are in the clustered index and how much space the view takes up.

EXECUTE sp_spaceused 'vw_SalesByProduct_Indexed'

The SELECT statement below is the same statement as before, except this time it performs a clustered index seek, which is typically very fast.

SELECT 
      Product, TotalSales, ProductCount
FROM vw_SalesByProduct_Indexed
WHERE Product = 'Computer'

Don't forget performance testing

Indexed views are great when you use them in the right situations because they can drastically improve the performance of queries. However, due to the added performance gain from the clustered index, the database engine must maintain that index throughout all of the transactions occurring on the base tables of the view. Because of this tradeoff, creating an index view may or may not be of benefit to your system. The best way to determine this is through thorough performance testing.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

By Tim Chapman

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.