MS SQL entry basis: Index view

We have already mentioned in front of the view the view: view the result set returned with the ranks of the structure of the table has the same tabular form, and we can in a SQL statement like reference table that references the view, so we often The view is called the virtual table. The standard view of the result set is not stored in the database in the form of a table, but the references in the implementation of the view query, SQL Server 2000 and only then the base table data is merged into the logical structure of the view.

At the conclusion often produce such anxiety: in the implementation of the view query references the logical structure of the view, then merge the data into SQL Server 2000 and only then the base table referenced by the query view contains a large number of data rows or related to a large number of rows of data aggregating operations or connect operation, no doubt, view the result set will give the system to dynamically create a heavy burden, especially frequent references This large-capacity view.

The solution to this vexing problem is the view of creating a clustered index, the only way to significantly improve the performance of the system. When a clustered index is created on a view, the view of the result set has a clustered index table data set is stored as data.

In addition, created on the view index will also bring benefits; optimizer can not directly named in the FROM clause view query using the view index, but do not have to re-write the query code view , thereby improving the efficiency of query data is retrieved from the index view.

If you create an index on a view, the view data is immediately stored in the database, modify the index view, these changes will be immediately reflected in the underlying table. Similarly, data on the underlying table changes will be reflected in the index view there. The index uniqueness of greatly improving the SQL Server Find modify rows of data.

Maintain the indexed view is more complex than the maintenance of the index of the underlying table. So, if you think it is worth to improve to data modification increases the burden on the system at the expense of the speed of data retrieval, you should create an index on a view. Create an index for the view, the view must meet the following conditions: CREATE VIEW command ANSI_NULLS and QUOTED_IDENTIFIER option must be set to ON state; ANSI_NULLS option should be set to use the CREATE TABLE command to create the underlying table referenced by the index, ON; objects referenced by the view includes only the underlying table does not include the other view; underlying tables referenced by the view must belong to the same database view and have the same owner; SCHEMABINDING option must be used when creating the view; view references a user-defined function, then create these user-defined functions must also use SCHEMABINDING option; the view must the form of owner.objectname to make the referenced table or user-defined function; functions must be referenced by the view uncertainty; Also worth noting is that in the SELECT statement to create a view should not contain the following Transact-SQL syntax; * or TableName SELECT statement can not be used. * to define the column, the column name must be given directly, or do not ; rowset function; UNION, DISTINCT, TOP, ORDER BY, COMPUTE, COMPUTE BY, COUNT (*), etc.; AVG, MAX, MIN, STDEV, STDEVP, VAR and VARP total computing function.

Generally speaking, you can create multiple indexes on a view, but it should be remembered, the first index created on the view must be a clustered index before you can create other non-clustered index.

In the eighth chapter we have described in detail the CREATE INDEX command but if you are going to create indexes on views then before executing the command, you must ensure that the following conditions are otherwise create will fail.CREATE INDEX command must be run by the owner of the view; during create index command is executed, the ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, the ARITHABORT CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIERS various options should be set to the ON state; NUMERIC_ROUNDABORT option is set to OFF state; view can not including text, ntext, image type columns of data; view SELECT statement contains an ORDER BY option, the clustered index key value data columns can only be defined in the ORDER BY clause.