Artículo
03/30/2012

Crear vistas indizadas

Antes de poder crear un índice agrupado en una vista, ésta debe cumplir los requisitos siguientes:

Las opciones ANSI_NULLS y QUOTED_IDENTIFIER deben establecerse en ON cuando se ejecute la instrucción CREATE VIEW. La función OBJECTPROPERTY notifica esto a las vistas mediante las propiedades ExecIsAnsiNullsOn o ExecIsQuotedIdentOn.
La opción ANSI_NULLS debe establecerse en ON para poder ejecutar todas las instrucciones CREATE TABLE, que crean tablas a las que se hace referencia en la vista.
La vista no debe hacer referencia a ninguna otra vista, sólo a tablas base.
Todas las tablas base a las que hace referencia la vista deben estar en la misma base de datos que ésta y tener su mismo propietario.
Esta vista se debe crear con la opción SCHEMABINDING. El enlace de esquemas asocia la vista al esquema de las tablas base subyacentes.
Las funciones definidas por el usuario a las que se hace referencia en la vista se deben crear con la opción SCHEMABINDING.
Es preciso utilizar nombres compuestos de dos partes en la vista para hacer referencia a las tablas y las funciones definidas por el usuario. No se permiten nombres de una, tres y cuatro partes.
Todas las funciones a las que se hace referencia en las expresiones de la vista deben ser deterministas. La propiedad IsDeterministic de la función OBJECTPROPERTY notifica si una función definida por el usuario es determinista. Para obtener más información, vea Funciones deterministas y no deterministas.

Nota

Cuando haga referencia a los literales de cadena datetime y smalldatetime de las vistas indizadas de SQL Server 2008, se recomienda convertir explícitamente el literal al tipo de datos deseado mediante un estilo de formato de fecha determinista.Para consultar una lista de los estilos de formato de fecha deterministas, vea CAST y CONVERT (Transact-SQL). Las expresiones que impliquen la conversión implícita de cadenas de caracteres a datetime o smalldatetime se considerarán no deterministas, a menos que el nivel de compatibilidad se establezca en 80 o menos. Esto se debe a que los resultados dependen de los valores LANGUAGE y DATEFORMAT de la sesión de servidor. Por ejemplo, los resultados de la expresión CONVERT (datetime, '30 listopad 1996', 113) dependen del valor de LANGUAGE porque la cadena 'listopad' significa distintos meses en distintos idiomas. De forma similar, en la expresión DATEADD(mm,3,'2000-12-01'), SQL Server interpretará la cadena '2000-12-01' en función del valor de DATEFORMAT.

La conversión implícita de datos de caracteres no Unicode entre intercalaciones también se considera no determinista, a menos que el nivel de compatibilidad se establezca en 80 o anterior.

La creación de índices en vistas que contienen estas expresiones no se admite en el modo de compatibilidad 90. Sin embargo, las vistas existentes que contienen estas expresiones procedentes de una base de datos actualizada se pueden mantener. Si utiliza vistas indizadas que contienen conversiones implícitas de cadena a fecha, asegúrese de que las opciones LANGUAGE y DATEFORMAT son coherentes en las bases de datos y las aplicaciones, para evitar posibles daños en las vistas indizadas.
Si la definición de la vista utiliza una función de agregado, la lista SELECT también debe incluir COUNT_BIG (*).
La propiedad de acceso a datos de una función definida por el usuario debe ser NO SQL y la propiedad de acceso externo debe ser NO.
Las funciones de Common Language Runtime (CLR) pueden aparecer en la lista de selección de la vista, pero no pueden formar parte de la definición de la clave de índice agrupado. Las funciones CLR no pueden aparecer en la cláusula WHERE de la vista ni en la cláusula ON de una operación JOIN en la vista.

Los métodos y las funciones CLR de tipos definidos por el usuario CLR utilizados en la definición de la vista deben establecer las propiedades según se indica en la tabla siguiente.

Propiedad	Nota
DETERMINISTIC = TRUE	Debe declararse de forma explícita como un atributo del método Microsoft .NET Framework.
PRECISE = TRUE	Debe declararse de forma explícita como un atributo del método .NET Framework.
DATA ACCESS = NO SQL	Se determina mediante la definición del atributo DataAccess como DataAccessKind.None y del atributo SystemDataAccess como SystemDataAccessKind.None.
EXTERNAL ACCESS = NO	Esta propiedad tiene el valor predeterminado NO en rutinas CLR.

Para obtener más información sobre cómo definir atributos de métodos de rutinas CLR, vea Atributos personalizados para las rutinas de CLR.

Advertencia
No se recomienda establecer las propiedades de los métodos de rutinas CLR de forma opuesta al funcionamiento del método. Esto podría provocar daños en los datos.

La instrucción SELECT de la vista no puede contener los siguientes elementos de sintaxis Transact-SQL:
- La sintaxis * o table_name**.*** para especificar columnas. Los nombres de columnas deben especificarse explícitamente.
- Un nombre de columna de tabla utilizado como una expresión sencilla no puede especificarse en más de una columna de vista. Se puede hacer referencia a una columna varias veces siempre que todas las referencias a la columna (o todas menos una) formen parte de una expresión compleja o de un parámetro de una función. Por ejemplo, la lista de selección siguiente no es válida:
```
SELECT ColumnA, ColumnB, ColumnA
```
  Esta lista de selección sí es válida:
```
SELECT SUM(ColumnA) AS SumColA, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ModuloColAColB
```
- Una expresión en una columna utilizada en la cláusula GROUP BY o una expresión en los resultados de un agregado.
- Una tabla derivada.
- Una expresión de tabla común (CTE).
- Funciones de conjunto de filas.
- Operadores UNION, EXCEPT o INTERSECT.
- Subconsultas.
- Combinaciones externas y autocombinaciones.
- Cláusula TOP.
- Cláusula ORDER BY.
- Palabra clave DISTINCT.
- COUNT; se permite COUNT_BIG(*).
- Las funciones de agregado AVG, MAX, MIN, STDEV, STDEVP, VAR o VARP. Si se especifica AVG(expression) en consultas que hacen referencia a la vista indizada, el optimizador puede calcular con frecuencia el resultado requerido cuando la lista de selección de la vista contiene SUM(expression) y COUNT_BIG(expression). Por ejemplo, una lista SELECT de una vista indizada no puede contener la expresión AVG(column1). Si la lista SELECT de la vista contiene las expresiones SUM(column1) y COUNT_BIG(column1), SQL Server puede calcular el promedio de una consulta que hace referencia a la vista y especifica AVG(column1).
- Una función SUM que hace referencia a una expresión que admite valores NULL.
- La cláusula OVER, que incluye funciones de agregado o de clasificación.
- Una función de agregado definida por el usuario CLR.
- Los predicados de texto CONTAINS o FREETEXT.
- Las cláusulas COMPUTE o COMPUTE BY.
- Los operadores CROSS APPLY u OUTER APPLY.
- Los operadores PIVOT o UNPIVOT
- Sugerencias de tabla (válido únicamente en un nivel de compatibilidad de 90 o superior).
- Sugerencias de combinación.
- Referencias directas a expresiones Xquery. Se aceptan referencias indirectas, como expresiones Xquery dentro de una función enlazada a esquema definida por el usuario.
Si se especifica GROUP BY, la lista de selección de la vista debe contener una expresión COUNT_BIG(*) y la definición de vista no puede especificar HAVING, ROLLUP, CUBE ni GROUPING SETS.

Requisitos para la instrucción CREATE INDEX

El primer índice creado en una vista debe ser un índice agrupado único. Después de haber creado el índice agrupado único, puede crear índices no agrupados adicionales. Las convenciones de nomenclatura para los índices en las vistas son las mismas que para los índices en tablas. La única diferencia es que se reemplaza el nombre de la tabla con un nombre de vista. Para obtener más información, vea CREATE INDEX (Transact-SQL).

La instrucción CREATE INDEX debe cumplir los siguientes requisitos además de los requisitos de CREATE INDEX habituales:

El usuario que ejecute la instrucción CREATE INDEX debe ser el propietario de la vista.
Cuando se ejecuta la instrucción CREATE INDEX, se deben establecer en ON las siguientes opciones SET:
- ANSI_NULLS
- ANSI_PADDING
- ANSI_WARNINGS
- CONCAT_NULL_YIELDS_NULL
- QUOTED_IDENTIFIER
La opción NUMERIC_ROUNDABORT debe establecerse en OFF. Ésta es la configuración predeterminada.
Si la base de datos se ejecuta en el modo de compatibilidad 80 o inferior, la opción ARITHABORT se debe establecer en ON.
Cuando crea un índice agrupado o no agrupado, la opción IGNORE_DUP_KEY debe establecerse en OFF (configuración predeterminada).
La vista no puede incluir columnas text, ntext ni image, aunque no se haga referencia a ellas en la instrucción CREATE INDEX.
Si la instrucción SELECT de la definición de vista especifica una cláusula GROUP BY, la clave del índice agrupado único sólo puede hacer referencia a las columnas especificadas en dicha cláusula.
Una expresión imprecisa que forme el valor de una columna de clave de índice debe ser una referencia a una columna almacenada en una tabla base subyacente de la vista. Esta columna puede ser una columna almacenada normal o una columna calculada persistente. Ninguna otra expresión imprecisa puede formar parte de la columna de clave de una vista indizada.

Consideraciones

El valor de la opción large_value_types_out_of_row en las columnas de una vista indizada se hereda de la configuración de la columna correspondiente en la tabla base. Este valor se establece con sp_tableoption. La configuración predeterminada de las columnas formadas a partir de expresiones es 0; esto significa que los tipos de valor grande se almacenan en la fila. Para obtener más información, vea Usar tipos de datos de valores grandes.

Una vez que se ha creado el índice agrupado, toda conexión que trate de modificar los datos base de la vista debe tener los mismos valores de opción requeridos para crear el índice. SQL Server genera un error y revierte las instrucciones INSERT, UPDATE o DELETE que vayan a afectar al conjunto de resultados de la vista si la conexión que ejecuta la instrucción no tiene los valores de opción correctos. Para obtener más información, vea Opciones SET que afectan a los resultados.

Si se quita la vista, todos sus índices se quitan. Todos los índices no agrupados y las estadísticas creadas automáticamente de una vista se quitan si se quita el índice agrupado. Las estadísticas creadas por el usuario de la vista se conservan. Los índices no agrupados se pueden quitar individualmente. Quitar el índice agrupado de la vista quita el conjunto de resultados almacenado; el optimizador vuelve a procesar la vista como una vista estándar.

Aunque en la instrucción CREATE UNIQUE CLUSTERED INDEX sólo se especifican las columnas que conforman la clave del índice agrupado, en la base de datos se almacena el conjunto de resultados completo de la vista. Al igual que en un índice agrupado en una tabla base, la estructura de árbol b del índice agrupado sólo contiene las columnas de clave, pero las filas de datos contienen todas las columnas en el conjunto de resultados de la vista.

Para agregar índices a las vistas de un sistema existente, asocie al esquema cualquier vista en la que desee incluir un índice. Puede realizar las siguientes operaciones:

Quitar la vista y volver a crearla especificando WITH SCHEMABINDING.
Puede crear una segunda vista que tenga el mismo texto que la vista existente, pero con un nombre diferente. El optimizador tiene en cuenta los índices de la nueva vista, aunque no se haga referencia directa a ellos en la cláusula FROM de las consultas.

Nota

Las vistas o las tablas que participan en una vista creada con la cláusula SCHEMABINDING no se pueden quitar, a menos que se quite o cambie esa vista de forma que deje de tener un enlace de esquema. Además, las instrucciones ALTER TABLE en tablas que participan en vistas con enlaces de esquemas provocarán un error si dichas instrucciones afectan a la definición de la vista.

Debe asegurarse de que la nueva vista cumple todos los requisitos de una vista indizada. Esto puede obligarle a cambiar la propiedad de la vista y de todas las tablas base a las que hace referencia, de manera que todas pertenezcan al mismo usuario.

Los índices de las tablas y las vistas se pueden deshabilitar. Cuando se deshabilita un índice agrupado de una tabla, también se deshabilitan los índices de las vistas asociadas a la tabla. Para obtener más información, vea Deshabilitar índices.

Ejemplo

En el ejemplo siguiente se crea una vista y un índice en esa vista. Se incluyen dos consultas que utilizan la vista indizada.

USE AdventureWorks2008R2;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
    WHERE od.SalesOrderID = o.SalesOrderID
    GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is 
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
    OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO