As you already know from Chapter 10, there are several special index types. One of them is the indexed view, the topic of this section.
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 (demonstrated in 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 base table with a clustered index is stored. This means that the leaf nodes of the clustered index’s B+-tree contain data pages (see also the description of the clustered table in Chapter 10).
NOTE Indexed views are implemented through syntax extensions to the CREATE INDEX and CREATE VIEW statements. In the CREATE INDEX statement, you specify the name of a view instead of a table name. The syntax of the CREATE VIEW statement is extended with the SCHEMABINDING clause. For more information on extensions to this statement, see the description in Chapter 11.
1. Creating an Indexed View
Creating an indexed view is a two-step process:
- Create the view using the CREATE VIEW statement with the SCHEMABINDING clause.
- Create the corresponding unique clustered index.
Example 26.12 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.)
Example 26.12
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;
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 26.12. 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 26.13 shows.
Example 26.13
USE sample;
GO
CREATE UNIQUE CLUSTERED INDEX
c_workson_deptno ON v_enter_month (enter_month, emp_no);
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 26.14. If the value of the function is 1, all requirements are met and you can create the clustered index.
Example 26.14
USE sample;
SELECT objectproperty(object_id(‘v_enter_month’), ‘IsIndexable’);
2. 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 traditional one.
NOTE If you drop an indexed view, all indices on that view are dropped.
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.
NOTE If you create a new view with a different name, you must ensure that the new view meets all the requirements for an indexed view that are described in the preceding section.
3. Editing Information Concerning Indexed Views
You can use the sessionproperty property function to test whether one of the options of the SET statement is activated (see the earlier section “Creating an Indexed View” for a list of the options). If the function returns 1, the setting is ON. Example 26.15 shows the use of the function to check how the QUOTED_IDENTIFIER option is set.
Example 26.15
SELECT sessionproperty (‘QUOTED_IDENTIFIER’);
The easier way is to use the dynamic management view called sys.dm_exec_sessions, because you can retrieve values of all options discussed previously using only one query. (Again, if the value of a column is 1, the corresponding option is set to ON.) Example 26.16 returns the values for the first four SET statement options from the list in “Creating an Indexed View” (The global variable @@spid is described in Chapter 4.)
Example 26.16
USE sample;
SELECT quoted_identifier, concat_null_yields_null, ansi_nulls, ansi_padding
FROM sys.dm_exec_sessions
WHERE session_id = @@spid;
The sp_spaceused system procedure allows you to check whether the view is materialized—that is, whether or not it uses the storage space. The result of Example 26.17 shows that the v_enter_month view uses storage space for the data as well as for the defined index.
Example 26.17
USE sample;
EXEC sp_spaceused ‘v_enter_month’;
The result is
4. Benefits of Indexed Views
Besides possible performance gains for complex views that are frequently referenced in queries, the use of indexed views has two other advantages:
- The index of an indexed view can be used even if the view is not explicitly referenced in the FROM clause.
- All modifications to data are reflected in the corresponding indexed view.
Probably the most important property of indexed views is that a query does not have to explicitly reference a view to use the index on that view. In other words, if the query contains references to columns in the base table(s) that also exist in the indexed views, and the optimizer estimates that using the indexed view is the best choice, it chooses the view indices in the same way it chooses table indices when they are not directly referenced in a query.
When you create an indexed view, the result set of the view (at the time the index is created) is stored on the disk. Therefore, all data that is modified in the base table(s) will also be modified in the corresponding result set of the indexed view.
Besides all the benefits that you can gain by using indexed views, there is also a (possible) disadvantage: indices on indexed views are usually more complex to maintain than indices on base tables, because the structure of a unique clustered index on an indexed view is more complex than the structure of the corresponding index on a base table.
The following types of queries can achieve significant performance benefits if a view that is referenced by the corresponding query is indexed:
- Queries that process many rows and contain join operations or aggregate functions
- Join operations and aggregate functions that are frequently performed by one or several queries
If a query references a standard view and the database system has to process many rows using the join operation, the optimizer will usually use a suboptimal join method. However, if you define a clustered index on that view, the performance of the query could be significantly enhanced, because the optimizer can use an appropriate method. (The same is true for aggregate functions.)
If a query that references a standard view does not process many rows, the use of an indexed view could still be beneficial if the query is used very frequently. (The same is true for groups of queries that join the same tables or use the same type of aggregates.)
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.