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 11.22 shows the use of the function to check how the QUOTED_IDENTIFIER option is set.
SELECT sessionproperty ('QUOTED_IDENTIFIER');Code language: JavaScript (javascript)The easier way is to use the dynamic management view called sys.dm_exec_sessions, because you can retrieve values of all options discussed above using only one query. (Again, if the value of a column is 1, the corresponding option is set to ON.) Example 11.23 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 section “Scalar Operators, Global Variables“.)
USE sample;
SELECT quoted_identifier, concat_null_yields_null, ansi_nulls, ansi_padding
FROM sys.dm_exec_sessions
WHERE session_id = @@spid;Code language: PHP (php)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 11.24 shows that the v_enter_month view uses storage space for the data as well as for the defined index.
USE sample;
EXEC sp_spaceused 'v_enter_month';Code language: PHP (php)The result is
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 a 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 a 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.)
