SQL 2005+

SQL Server Indexed Views

by Richard Carr, published at http://www.blackwasp.co.uk/SQLIndexedViews.aspx

The twenty-second part of the SQL Server Programming Fundamentals tutorial describes indexed views. An indexed view is simply a view that has a unique clustered index and, optionally, one or more non-clustered indexes defined to improve query performance.

What is an Indexed View?

When you create a view, you add a stored query to the database that can be read in the same manner as a table. The view acts as a virtual table that requires no additional storage space, as the information is read directly from the tables.

A view's performance can be significantly worse than that of a table, particularly when the view reads from several, joined tables. To improve performance you can create indexed views. These are views that have a unique, clustered index applied. Indexed views can also use non-unique and non-clustered indexes but only if a unique, clustered index exists.

When the first index is added to a view, it is built using a b-tree structure similar to that of a table index. The b-tree includes the data from the columns that are present in the index. However, unlike a table index, all of the columns that are included in the view are also added to the data rows of the index. This can lead to large increases in the storage requirements for a database but improves the view's performance.

Limitations of Indexed Views

Indexed views have additional limitations to other, non-indexed views. The key restrictions are listed below. There are other limitations that are not described in this tutorial. You should refer to the Microsoft documentation for further details.

• Indexed views must be created using the SCHEMABINDING option. This option links the view to the schema elements that it uses, disallowing changes to the underlying tables that would break the view. If the view references user-defined functions, these too must be created using the option.
• Indexed views may only select columns from base tables. They may not retrieve values from other views. The tables must be in the same database as the view and must have the same owner.
• The columns in the view must be explicitly listed in the view's query. You cannot use the asterisk character to specify that you wish to retrieve all columns from a table, so queries such as "SELECT * FROM MyTable" are not permitted.
• Indexed views may include inner joins but outer joins are not permitted.
• Any functions used in the view's query must be deterministic.

Adding an Indexed View to a Database

In this article we will add an indexed view to the JoBS tutorial database. This database has been constructed and populated with data throughout the course of the tutorial. If you do not have an up-to-date copy of the database, download the script from the top of this page and execute it to create a new version.

Adding a View

To create an indexed view, you must first create a standard view with the SCHEMABINDING option. In this case, we will use Transact-SQL to create a sample view in the JoBS database. The view will list all of the jobs that are linked to an annual contract, rather than those that were purchased as a single job. These rows are those linked to a contract with no renewal date. The view will return the job number, contract number, standard job name, price and the value of the linked contract. This view could be the source of a report that determines that profitability of contracts.

To create the view, run the following T-SQL statement. Note that the table names are expressed in the two-part format, as this is required when creating views with schema binding enabled.

CREATE VIEW ContractJobs WITH SCHEMABINDING AS
SELECT
    J.JobId,
    J.ContractNumber,
    SJ.JobName,
    SJ.StandardPrice,
    C.ContractValue
FROM
    dbo.Jobs J
INNER JOIN
    dbo.StandardJobs SJ
ON
    J.StandardJobId = SJ.StandardJobId
INNER JOIN
    dbo.Contracts C
ON
    J.ContractNumber = C.ContractNumber
WHERE
    C.RenewalDate IS NOT NULL

Creating a Unique Clustered Index

Once the view has been created, indexes may be added to it. The first index that you create must be a unique index and must be clustered. This index is used to store the data in the view and will be updated whenever the data in the underlying tables is changed. In the ContractJobs view, the JobId is the only suitable candidate key for the index. To create the index, execute the following T-SQL:

CREATE UNIQUE CLUSTERED INDEX IX_ContractJobs_JobId ON ContractJobs
(
    JobId
)

Creating Additional Indexes

Once a unique clustered index is present for a view, further non-clustered indexes may be created. For example, to add an index for the ContractNumber column, you could use the following command:

CREATE INDEX IX_ContractJobs_ContractNumber ON ContractJobs
(
    ContractNumber
)