Indexed Views – Creating an Indexed View

As you already know from the previous chapter, there are several special index types. One of them is the indexed view, which will be described next.

A view always contains a query that acts as a filter. Without indices created for a particular view, the Database Engine builds dynamically the result set from each query that references a view. (“Dynamically” means that if you modify the content of a table, the corresponding view will always show the new information.) Also, if the view contains computations based on one or more columns of the table, the computations are performed each time you access the view.

Building dynamically the result set of a query can decrease performance, if the view with its SELECT statement processes many rows from one or more tables. If such a view is frequently used in queries, you could significantly increase performance by creating a clustered index on the view (see the next section). Creating a clustered index means that the system materializes the dynamic data into the leaf pages on an index structure.

The Database Engine allows you to create indices on views. Such views are called indexed or materialized views. When a unique clustered index is created on a view, the view is executed and the result set is stored in the database in the same way a table with a clustered index is stored. This means that the leaf nodes of the clustered index’s B+- tree contain data pages.

Creating an Indexed View

Creating an indexed view is a two-step process:

1. Create the view using the CREATE VIEW statement with the SCHEMABINDING clause.
2. Create the corresponding clustered index.

Example 11.19 shows the first step, the creation of a typical view that can be indexed to gain performance. (This example assumes that works_on is a very large table.)

USE sample;
GO
CREATE VIEW v_enter_month
	WITH SCHEMABINDING
	AS SELECT emp_no, DATEPART(MONTH, enter_date) AS enter_month
		FROM dbo.works_on;Code language: PHP (php)

The works_on table in the sample database contains the enter_date column, which represents the starting date of an employee in the corresponding project. If you want to retrieve all employees that entered their projects in a specified month, you can use the view in Example 11.19. To retrieve such a result set using index access, the Database Engine cannot use a table index, because an index on the enter_date column would locate the values of that column by the date, and not by the month. In such a case, indexed views can help, as Example 11.20 shows.

USE sample;
GO
CREATE UNIQUE CLUSTERED INDEX
	c_workson_deptno ON v_enter_month (enter_month, emp_no);Code language: PHP (php)

To make a view indexed, you have to create a unique clustered index on the column(s) of the view. (As previously stated, a clustered index is the only index type that contains the data values in its leaf pages.) After you create that index, the database system allocates storage for the view, and then you can create any number of nonclustered indices because the view is treated as a (base) table.

An indexed view can be created only if it is deterministic—that is, the view always displays the same result set. In that case, the following options of the SET statement must be set to ON:

• QUOTED_IDENTIFIER
• CONCAT_NULL_YIELDS_NULL
• ANSI_NULLS
• ANSI_PADDING
• ANSI_WARNINGS

Also, the NUMERIC_ROUNDABORT option must be set to OFF.

There are several ways to check whether the options in the preceding list are appropriately set, as discussed in the upcoming section “Editing Information Concerning Indexed Views.”

To create an indexed view, the view definition has to meet the following requirements:

• All referenced (system and user-defined) functions used by the view have to be deterministic—that is, they must always return the same result for the same arguments.
• The view must reference only base tables.
• The view and the referenced base table(s) must have the same owner and belong to the same database.
• The view must be created with the SCHEMABINDING option. SCHEMABINDING binds the view to the schema of the underlying base tables.
• The referenced user-defined functions must be created with the SCHEMABINDING option.
• The SELECT statement in the view cannot contain the following clauses and options: DISTINCT, UNION, TOP, ORDER BY, MIN, MAX, COUNT, SUM (on a nullable expression), subqueries, derived tables, or OUTER.

The Transact-SQL language allows you to verify all of these requirements by using the IsIndexable parameter of the objectproperty property function, as shown in Example 11.21. If the value of the function is 1, all requirements are met and you can create the clustered index.

USE sample;
SELECT objectproperty(object_id('v_enter_month'), 'IsIndexable');Code language: PHP (php)

Modifying the Structure of an Indexed View

To drop the unique clustered index on an indexed view, you have to drop all nonclustered indices on the view, too. After you drop its clustered index, the view is treated by the system as a convenient view.

If you want to change a standard view to an indexed one, you have to create a unique clustered index on it. To do so, you must first specify the SCHEMABINDING option for that view. You can drop the view and re-create it, specifying the SCHEMABINDING clause in the CREATE SCHEMA statement, or you can create another view that has the same text as the existing view but a different name.