MS SQL Basics Tutorial: Indexed view

As we've mentioned in the previous view, because the view returns a result set that has the same tabular form as a table with a row and column structure, and we can reference the view in the SQL statement like a reference table, we often refer to the view as a virtual table. The result set of the standard view is not stored in the database as a table, but SQL Server 2000 merges the data from the underlying tables into the logical structure of the view when the query that references the view is executed.

The above conclusion often causes us to have this kind of anxiety: Since SQL Server 2000 merges the data from the underlying tables into the logical structure of the view when a query referencing the view is executed, the view that the query refers to contains a large number of rows of data or involves a large number of rows of data or connection operations , there is no doubt that dynamically creating a view result set will put a heavy burden on the system, especially if you often refer to this large-capacity view.

The solution to this headache problem is to create a clustered index for the view, which will significantly improve the performance of the system. When a clustered index is created on a view, the result set of the view is stored in the data as the dataset of the table with the clustered index.

In addition, creating an index on a view can also provide such benefits; the optimizer can use the view index in queries that do not directly name the view in the FROM clause, and it improves the efficiency of the query's retrieval of data from the indexed view, because there is no need to rewrite the query code.

If you create an index on a view, the data in the view is immediately stored in the database, and the indexed view is modified so that the changes are immediately reflected in the underlying table. Similarly, data modifications made to the underlying table are also reflected in the indexed view. The uniqueness of the index greatly improves the SQL Server's search for those modified rows of data.

Maintaining an indexed view is more complex than maintaining the index of the underlying table. Therefore, if you think it is worthwhile to increase the speed of data retrieval at the expense of increasing the system burden due to data modification, you should create an index on the view. Before you can create an index for a view, the view itself must meet the following conditions: When you execute the CREATE VIEW command, you must set the ANSI_NULLS and QUOTED_IDENTIFIER options to on, and when you create the underlying table referenced by the index by using the Build Table command, The ANSI_NULLS option should be set to on; The view refers to objects that include only the underlying table and not other views; the underlying table referenced by the view must be the same database as the view and have the same owner, and the schemabinding option must be used when creating the view; If the view references user-defined functions, you must also use the SCHEMABINDING option when creating these user-defined functions; The view must be owner.objectname to make the referenced table or user custom function; The function referenced by the view must be deterministic; It is also noteworthy that the following Transact-SQL syntax should not be included in the SELECT statement that creates the view; you cannot use * or tablename.* to define columns in a SELECT statement, you must give a column name directly, otherwise you cannot; a function that represents a rowset; UNION, DISTINCT, top, order BY, COMPUTE, COMPUTE by, COUNT (*), AVG, MAX, MIN, STDEV, STDEVP, VAR, and VARP aggregate operational functions.

In general, you can create multiple indexes on a view, but it should be remembered that the first index created on the view must be a clustered index before you can create additional nonclustered indexes.

In the eighth chapter we have introduced the CREATE INDEX command in detail, but if you are ready to create an index for the view, you must ensure that the following conditions are not created before executing the command. The performer of the CREATE INDEX command must be the owner of the view, and during the execution of the CREATE INDEX command, ANSI_NULLS, ANSI_PADDING, Ansi_warnings, ARITHABORT, Concat_null_yields_ The NULL, quoted_identifiers options should be set to on State; the Numeric_roundabort option is set to OFF, the view cannot include text, ntext, image-type data columns, or if the view's SELECT statement contains a package With the order BY option, the key value of the clustered index can only be the data column defined in the ORDER BY clause.

After you create an index on a view, you should make sure that when you modify the view data, the option settings at the time of the modification are the same as the option settings when you created the index, otherwise SQL Server generates an error message and rolls back the insert, UPDATE, and delete operations.

See the full set of "MS SQL Basics Tutorials"