Indexed Views

SQL Server 2000 can create indexes on views. This functionality is implemented by extensions in the CREATE VIEW and CREATE INDEX statements. If a view is not indexed, it doesn't use any storage space. Whenever you use the view in a Transact-SQL statement, SQL Server merges the view definition with the statement to produce a single execution plan and it directly accesses the underlying tables on which the view is defined.

After a view is indexed, its index needsstorage space, as any standard index.

The process of creating an indexed view is as follows:

After the creation of the clustered index, the view is stored like a clustered index for a table, but this information is maintained automatically whenever data changes in the underlying tables.

If you reference the view, Query Optimizer will use the indexed view directly, and it will be unnecessary to access the underlying tables.

SQL Server 2000 Enterprise Edition can use indexed views to optimize the execution of queries that don't reference the indexed views explicitly, improving execution performance.

Listing 6.34 shows how to create an index on a view. Figure 6.24 shows how SQL Server uses the view definition to access the base tables directly. Figure 6.25 shows that by having an indexed view, SQL Server can avoid accessing the base tables, reducing the amount of IO required to execute the query.

Code Listing 6.34. In SQL Server 2000, You Can Create Indexes on Views

					
USE Northwind
GO

-- Create the view

CREATE VIEW Customers_UK
WITH SCHEMABINDING
AS
SELECT CustomerID, CompanyName, ContactName, Phone
FROM dbo.Customers
WHERE Country = 'UK'

-- Test how a normal query uses the view

SELECT CustomerID, CompanyName, ContactName, Phone
FROM Customers
WHERE Country = 'UK'
AND CompanyName like 'C%'

-- Create a clustered index on the view

CREATE UNIQUE CLUSTERED INDEX CustUK ON Customers_UK (CustomerID)

-- Create a nonclustered index on the CompanyName field on the view

CREATE NONCLUSTERED INDEX CustUKCompany ON Customers_UK (CompanyName)

-- Test how a normal query uses the view after indexing the view

SELECT CustomerID, CompanyName, ContactName, Phone
FROM Customers
WHERE Country = 'UK'
AND CompanyName like 'C%'

Not every view can be indexed because some requirements must be met. Some requirements affect the definition of the view, and others the creation of the index. To create a view that can be indexed, it is necessary to meet the following requirements:

• The ANSI_NULLS option must be set to ON to create the base tables and the view.

• The QUOTED_IDENTIFIER must be set to ON to create the view.

• The view must reference only base tables, from the same database and owner.

• The view must be created with the SCHEMABINDING option set to prevent changes on the underlying objects. If the view uses a user-defined function, this must be created as well with SCHEMABINDING.

• To avoid ambiguity, objects must be referenced with two part names, owner, and object name. Three- or four-part names are not allowed because the view cannot reference objects from other databases or servers.

• All the expressions used in the view definition must be deterministic, and the expressions used in key columns must be precise, as explained earlier, for indexes on computed columns.

• The view must specifically name all columns, because SELECT * is not allowed.

• You cannot use a column more than once in the SELECT clause, unless every time the column was used as a part of a complex expression.

• It is not allowed to use subqueries, and that includes derived tables in the FROM clause.

• You cannot use Rowset functions, such as OPENROWSET, OPENQUERY, CONTAINSTABLE, or FREETEXTTABLE.

• It cannot contain the UNION operator.

• It cannot contain outer or self-joins.

• It cannot contain the ORDER BY clause, and that includes the use of the TOP clause.

• You cannot use the DISTINCT keyword.

• The only aggregate functions allowed are COUNT_BIG and SUM. To use the SUM function, you must select COUNT_BIG as well, and you must specify a GROUP BY clause.

• SUM cannot be used on a nullable column or expression.

• It cannot use full-text functions.

• COMPUTE or COMPUTE BY clauses are not allowed.

• The view cannot contain BLOB columns, as text, ntext, and image.

To create an index in a view, some more requirements must be met:

• Only the owner of the view can create indexes on the view.

• The connection settings must be the same as the settings for indexes on computed columns.

• If the view has a GROUP BY clause, only the columns specified in the GROUP BY clause can participate in the index key.