The view is also known as a virtual table because the result set returned by the view is similar to the table, which is similar to the column and rows, and the way the view is referenced in the SQL statement is the same as the reference table. The result set of the standard view is not permanently stored in the database. When queries each reference view, Microsoft® SQL ServerTM 2000 will dynamically merge the logic required to generate the view result set to generate the logic required to generate a complete query result set from the base table data. The process of generating view results is referred to as a view. For more information, see the view parsing. For standard views, the overhead of the dynamic generated result set for each reference view is large, especially those involving complex processing (such as aggregated large amounts of data or plugging many rows). This type of view is often referenced in the query, and the performance can be improved by creating a unique aggregation index on the view. This view is performed when you create a unique aggregation index on the view, and the result set is the same as the storage method of the table with the aggregated index.
Note You can create an index view only if the Microsoft SQL Server 2000 Enterprise Edition or Microsoft SQL Server 2000 development is installed.
Another benefit of creating an index on the view is: Query Optimizer starts using the view index in the query instead of naming a view directly in the FROM clause. In this way, the data can be retrieved from the index view without re-coding, the high efficiency brings to the existing query. For more information, see Using Index on the view. Creating a gathering index on the view stores data that exists when creating an index. The index view also automatically reflects the changes made to the base table data after the creation index is the same as the index created on the base table. When data in the base table is changed, the data stored in the index view also reflects the data changes. The aggregation index of the view must be unique, thereby increasing the efficiency of the row affected by any data change in the index. Maintenance of the index view may be more complex compared to the index on the base table. The index should be created on the view only when the results of the results retrieval speed exceeds the cost required for the modification. Such a view typically includes a view that maps to relatively static data, processing multiple rows, and references to many query references. The view is required to create a gathering index on the view:
When the CREATE VIEW statement is executed, the ANSI_NULLS and Quoted_IDentifier options must be set to ON. The ObjectProperty function reports this information as a view via the Execisansinullson or the ExecisquotedIdenton property. To perform all the CREATE TABLE statements to create a table referenced, the ANSI_NULLS option must be set to ON. The view cannot reference any other view, only the base table can be referenced. All base tables referenced by the view must be in the same database with the view, and the owner is also the same as the view. You must create a view using the SchemaBinding option. SchemaBinding bounds the view to the architecture of the base base table. You must have created a user-defined function referenced in the view using the SchemaBinding option. The tables and user-defined functions must be referenced by the name of the 2 part. Names of 1, 3 and 4 are not allowed. All functions referenced in the expressions in the view must be certain. The ISDeterministic property of the ObjectProperty function reports whether the user-defined function is determined. For more information, see the deterministic functions and non-definitective functions. The SELECT statement in the view does not contain the following Transact-SQL syntax elements:
The selection list cannot be used with * or Table_name. * Syntax specified column. The name must be explicitly given. The column names of the tables used as simple expressions cannot be specified in multiple view columns. If all of the columns (or only one exception) is a part of the complex expression or a parameter of the function, the column can be referenced multiple times. For example, the following selection list is illegal: Select Columna, Columnb, Columna The following selection list is legal: Select Columna, AVG (Columna), Columna Column B as AddColacolb
Select SUM (Column), ColumnA% Columnb As Modulecolacolb
Derived table. Rows of functions. UNION operator. Subproof. Outline or self-join. TOP clause. ORDER BY clause. Distinct keyword. COUNT (*) (Allow count_big (*).) AVG, MAX, Min, StDEV, STDEVP, VAR, or VARP aggregate functions. If AVG, MAX, Min, StDEV, StDEVP, VAR, MAX, MIN, STDEV, STDEVP, VAR, MAX, MIN, STDEV, STDEVP, VAR, MIN, MIN, STDEV, STDEVP, VAR, MIN, STDEV, STDEVP, VAR, MIN, and if the view selection list contains the following replacement functions, the optimizer will often calculate the required results.
Complex polymerization function alternative Simple polymerization function AVG (X) SUM (X), count_big (x) stdev (x) sum (x), count_big (x), sum (x ** 2) stdevp (x) SUM (X), Count_big (x), SUM (X) SUM (X), Count_big (x), Sum (x ** 2) VARP (X) SUM (X), Count_big (x), SUM (X ** 2) For example, an index view selection list cannot contain expression AVG (SomeColumn). If the view selection list contains Expression SUM (SomeColumn) and Count_Big (SomeColumn), SQL Server can calculate the average of the query of the reference view and specify the AVG (SomeColumn). References the SUM function that can be empty expression. All-text predicted word Contains or Fretext. Compute or compute BY clause. If you do not specify Group by, the view selection list cannot contain a polymeric expression. If Group B is specified, the view selection list must contain a count_big (*) expression, and the view definition cannot specify Having, Cube or Rollup. The columns generated by an expression that can be used as a FLOAT value can also be used to use the FLOAT expression. The first index of the CREATE INDEX statement must be a unique aggregation index. Other non-aggregated indexes can be created after creating a unique aggregation index. The index naming rules on the view are the same as the index naming rules on the table. The only difference is that the table name is replaced by the view name. See Create Index for more information. In addition to the general CREATE INDEX requirements, the CREATE INDEX statement must also meet the following requirements:
The user who executes the CREEX statement must be the owner of the view. When executing the CREATE INDEX statement, the following SET options must be set to ON:
ANSI_NULLS ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL Quoted_Identifier must set the option Numeric_Roundabort option to OFF. The view cannot contain text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement. If the SELECT statement in the view definition specifies a Group By clause, the only collected index of the index can only reference the columns specified in the Group By clause. Precautions After Creating a Gathering Index, for any connection to modify basic data attempting to make a view, its option settings must be the same as the options required to create an index. If the connection of this execution statement does not have an appropriate option setting, SQL Server generates errors and roll back to INSERT, UPDATE, or DELETE statements that will affect the view result set. For more information, see the SET option that affects the results. If you remove a view, all indexes on the view will also be removed. If you remove the aggregation index, all non-aggregated indexes on the view will also be removed. The non-aggregated index can be removed separately. Remove the aggregation index on the view will delete the stored result set, and the optimizer handles the view as the standard view. Although the CREATE UNIQUE Clustered Index statement specifies only columns that make up the collected index key, the complete result set of the view will be stored in the database. Like the aggregation index on the base table, the B tree structure of the aggregated index contains only the key columns, but the data line contains all the columns in the view result set. If you want to add an index for the view in the existing system, you must plan to bind any views that you want to put in an index. You can: remove the view and recreate it by specifying with schemaBinding. Create another view to have the same text as the existing view, but the name is different. The optimizer will consider the index on a new view, even if it is not directly referenced in the FROM clause of the query.
Note You or views involved in the views created with the Schemabinding clause are not removed unless the view has been removed or changed without the architectural binding. In addition, if the ALTER TABLE statement is performed on the table involved in the view with the schema binding, these statements will affect the view definition, and these statements will fail.
A new view must be ensured to meet all the requirements of the index view. This may need to change the ownership of all the base tables thereof so that they all have the same user. Transfer from: http://goaler.xicp.net/showlog.asp? Id = 526
Author Blog:
http://blog.9cbs.net/applebbs/
