VIGOTSKI: DIALÉCTICA DE LO REPRESENTACIONAL Y LO NOTACIONAL

Unlike previous versions of SQL Server, in SQL Server 2000 indexes can be created on a view, if its definition meets certain criteria. Unlike a nonindexed view, which does not physically hold data, an indexed view has its result physically stored in the database. Any modifications to the base data are reflected in the indexed view, so they are best created on tables that are changed infrequently.

The first index created on a view that is to be indexed must be a unique clustered index. Other indexes may then be created. For a view to be indexed it must satisfy a number of criteria. One criterion is that it must be created with the SCHEMABINDING option. This option binds the view to the schema of the underlying base tables. This means that any views or tables participating in the view cannot be dropped, unless that view is dropped or changed so that it no longer has schema binding. Also, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition. Some, but not all, of the other criteria are as follows:

• The view must only use base tables in its definition—no views.

• Any user-defined functions in the view must use the SCHEMABINDING option.

• The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON for the connection that defined the view.

• The ANSI_NULLS option must have been set to ON for the connection that defined the tables referenced by the view.

• The base tables referenced in the view must be in the same database and have the same database owner.

• Base tables and user-defined functions referenced in the view must use a two-part name. No other combination of names is allowed.

• All functions referenced by expressions in the view must be deterministic. This means that for a given set of inputs, the same result is always returned.

• The select_list of the SELECT statement in the view must not include the * notation— the columns must be listed explicitly.

• Columns must not appear more than once, unless they appear the second time (or third time, etc.) in a complex expression. The select_list Col1, Col2 is valid and so is Col1, Col2, Col1+Col2 but not Col1, Col2, Col1.

• Also not allowed are derived tables, rowset functions, the UNION operator,

subqueries, outer or self joins, the TOP clause, the ORDER BY clause, the DISTINCT keyword, and COUNT(*); however, COUNT_BIG(*) is allowed.

• The AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions. If AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP are specified in queries referencing the indexed view, the optimizer can often calculate the result if the view select_list contains SUM and COUNT_BIG. For example, AVG() can be calculated from SUM() / COUNT_BIG().

• A SUM function that references an expression that can be nullable is not allowed.

• The full-text search predicates CONTAINS or FREETEXT are not allowed.

• The view select_list cannot contain aggregate expressions unless a GROUP BY is present.

• If GROUP BY is present, the view select_list must contain a COUNT_BIG(*) expression, and the view definition cannot include HAVING, CUBE, or ROLLUP.

• A column that results from an expression that either evaluates to a float value or uses float expressions for its evaluation cannot be a key of an index in an indexed view. We've not finished yet! Indexes created on the view have some restrictions also, as shown in the following list. Most importantly, the first index that is created on the view must be clustered and unique.

• The user executing the CREATE INDEX statement must be the owner of the view.

• The following options must be set to ON for the connection creating the index: CONCAT_NULL_YIELDS_NULL, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, and ARITHABORT. The QUOTED_IDENTIFIERS and NUMERIC_ROUNDABORT options must be set to OFF.

• Even if the CREATE INDEX statement does not reference them, the view cannot include text, ntext, or image columns.

• If the SELECT statement in the view definition specifies a GROUP BY clause, then the key of the unique clustered index can reference only columns specified in the GROUP BY clause.

An example view definition is as follows: CREATE VIEW dbo.BranchTotalFunds WITH SCHEMABINDING AS SELECT branch_no, COUNT_BIG(*) AS AccountInstances, SUM(balance) AS TotalBalance FROM dbo.accounts GROUP BY branch_no

The following clustered index can now be created:

CREATE UNIQUE CLUSTERED INDEX CIV_BranchTotalFunds ON dbo.BranchTotalFunds (branch_no)

Although the clustered index key will only contain the branch_no column, being a clustered index, the complete set of data rows with all the columns will be stored at the clustered index leaf level in the database. Nonclustered indexes may also now be created on the indexed view if desired.

The query optimizer automatically makes use of indexed views—they do not have to be named explicitly—however, this is only true of the Enterprise Edition. We will discuss this behavior in Chapter 4.