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.
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.
