Creación de vistas indizadas

Article
07/25/2024

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

En este artículo se describe cómo crear índices en una vista. El primer índice creado en una vista debe ser un índice clúster único. Después de haber creado el índice clúster único, puede crear más índices no clúster. La creación de un índice clúster único en una vista mejora el rendimiento de las consultas, ya que la vista se almacena en la base de datos de la misma manera que se almacena una tabla con un índice agrupado. El optimizador de consultas puede utilizar vistas indizadas para acelerar la ejecución de las consultas. No es necesario hacer referencia a la vista en la consulta para que el optimizador tenga en cuenta esa vista para una sustitución.

Pasos

Para crear una vista indizada, es necesario seguir los pasos descritos a continuación, que son fundamentales para la correcta implementación de la vista indizada:

Compruebe que las opciones SET son correctas para todas las tablas existentes a las que se hará referencia en la vista.
Compruebe que las opciones SET de la sesión estén establecidas correctamente antes de crear cualquier tabla y la vista.
Compruebe que la definición de vista sea determinista.
Verifique que la tabla base y la vista tengan el mismo propietario.
Cree la vista con la opción WITH SCHEMABINDING.
Cree el índice clúster único en la vista.

Al ejecutar operaciones UPDATE, DELETE o INSERT (lenguaje de manipulación de datos o DML) en una tabla a la que hace referencia un gran número de vistas indexadas, o menos vistas indexadas complejas, esas vistas indizadas a las que se hace referencia también tienen que actualizarse. Como resultado, el rendimiento de la consulta DML se puede degradar notablemente o, en algunos casos, puede que tampoco se genere un plan de consulta.

En estos casos, pruebe las consultas DML antes de usarlas en entornos de producción, analice el plan de consulta y ajuste o simplifique la instrucción DML.

Opciones SET necesarias para las vistas indizadas

La evaluación de la misma expresión puede generar resultados diferentes en el motor de base de datos cuando hay distintas opciones SET activas cuando se ejecuta la consulta. Por ejemplo, después de establecer la opción SET CONCAT_NULL_YIELDS_NULL en ON, la expresión 'abc' + NULL devuelve el valor NULL. Sin embargo, al establecer CONCAT_NULL_YIELDS_NULL en OFF, la misma expresión genera abc.

Para asegurar el correcto mantenimiento de las vistas y la generación de resultados coherentes, las vistas indizadas requieren valores fijos para varias opciones SET. Las opciones SET de la tabla siguiente deben establecerse en los valores que se muestran en la columna Required value siempre que se produzcan las condiciones siguientes:

Se crean la vista y los índices siguientes en la vista.
Las tablas base a las que se hace referencia en la vista cuando se crea la tabla.
Cuando se realiza una operación de inserción, actualización o eliminación en cualquier tabla que participa en la vista indexada. Este requisito incluye operaciones como copia masiva, replicación y consultas distribuidas.
El optimizador de consultas utiliza la vista indizada para producir el plan de consulta.

Opciones de Set	Valor requerido	Valor de servidor predeterminado	Valor predeterminado Valor de OLE DB y ODBC	Valor predeterminado predeterminado
`ANSI_NULLS`	`ON`	`ON`	`ON`	`OFF`
`ANSI_PADDING`	`ON`	`ON`	`ON`	`OFF`
`ANSI_WARNINGS` ¹	`ON`	`ON`	`ON`	`OFF`
`ARITHABORT`	`ON`	`ON`	`OFF`	`OFF`
`CONCAT_NULL_YIELDS_NULL`	`ON`	`ON`	`ON`	`OFF`
`NUMERIC_ROUNDABORT`	`OFF`	`OFF`	`OFF`	`OFF`
`QUOTED_IDENTIFIER`	`ON`	`ON`	`ON`	`OFF`

¹ Si se establece ANSI_WARNINGS en ON, ARITHABORT se establece implícitamente en ON.

Si usa una conexión de servidor OLE DB u ODBC, el único valor que se debe modificar es la configuración de ARITHABORT. Todos los valores de DB-Library deben establecerse correctamente en el nivel de servidor mediante sp_configure o desde la aplicación mediante el comando SET.

Importante

Se recomienda encarecidamente establecer la opción de usuario en ARITHABORT todo el servidor ON en cuanto se cree la primera vista indizada o índice en una columna calculada en cualquier base de datos del servidor.

Requisito de vista determinista

La definición de una vista indizada debe ser determinista. Una vista es determinista si todas las expresiones de la lista de selección y las cláusulas WHERE y GROUP BY son deterministas. Las expresiones deterministas siempre devuelven el mismo resultado cada vez que se evalúan con un conjunto específico de valores de entrada. Solo las funciones deterministas pueden participar en expresiones deterministas. Por ejemplo, la función DATEADD es determinista porque siempre devuelve el mismo resultado para cualquier conjunto dado de valores de argumento para sus tres parámetros. GETDATE no es determinista porque siempre se invoca con el mismo argumento, pero el valor que devuelve cambia cada vez que se ejecuta.

Para determinar si una columna de vista es determinista, use la propiedad IsDeterministic de la función COLUMNPROPERTY. Para determinar si una columna determinista en una vista con enlace de esquema es precisa, use la propiedad IsPrecise de la función COLUMNPROPERTY. COLUMNPROPERTY devuelve 1 si TRUE, 0 si FALSE y NULL para la entrada que no es válida. Esto significa que la columna no es determinista o no es precisa.

Aun cuando una expresión sea determinista, si contiene expresiones de tipo float, es posible que un resultado exacto dependa de la arquitectura de procesador o de la versión de microcódigo. Para asegurar la integridad de los datos, estas expresiones solo pueden participar como columnas que no son de clave de vistas indizadas. Las expresiones deterministas que no contienen expresiones float se denominan precisas. Solo las expresiones deterministas precisas pueden participar en columnas de clave y en cláusulas WHERE o GROUP BY de vistas indizadas.

Requisitos adicionales

También se deben cumplir los siguientes requisitos, además de las opciones SET y los requisitos de función deterministas.

El usuario que ejecuta CREATE INDEX debe ser el propietario de la vista.
Al crear el índice, la opción de índice IGNORE_DUP_KEY debe establecerse en OFF (la configuración predeterminada).
En la definición de vista, se debe hacer referencia a las tablas mediante nombres de dos partes, <schema>.<tablename>.
Las funciones definidas por el usuario a las que se hace referencia en la vista se deben crear con la opción WITH SCHEMABINDING.
Para hacer referencia a las funciones definidas por el usuario a las que se hace referencia en la vista se deben usar nombres de dos partes, <schema>.<function>.
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 agrupada. Las funciones CLR no pueden aparecer en la cláusula WHERE de la vista o 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 de Microsoft .NET Framework.
PRECISE = TRUE	Debe declararse de forma explícita como un atributo del método de .NET Framework.
DATA ACCESS = NO SQL	Determinado estableciendo el atributo `DataAccess` en `DataAccessKind.None` y el atributo `SystemDataAccess` en `SystemDataAccessKind.None`.
EXTERNAL ACCESS = NO	Esta propiedad tiene el valor predeterminado NO en rutinas CLR.

La vista se debe crear mediante la opción WITH SCHEMABINDING.
La vista solo debe hacer referencia a tablas base que estén en la misma base de datos que la vista. La vista no puede hacer referencia a otras vistas.
Si GROUP BY está presente, la definición de VIEW debe contener COUNT_BIG(*), pero no HAVING. Estas restricciones GROUP BY solo se pueden aplicar a la definición de vista indizada. Una consulta puede usar una vista indizada en su plan de ejecución aunque no cumpla estas restricciones GROUP BY.
Si la definición de vista contiene una cláusula GROUP BY, la clave del índice clúster único solo puede hacer referencia a las columnas especificadas en la cláusula GROUP BY.

La instrucción SELECT de la definición de vista no debe contener la siguiente sintaxis de Transact-SQL:

Función de Transact-SQL	Posibles alternativas
`COUNT`	Use `COUNT_BIG`
Funciones `ROWSET` (`OPENDATASOURCE`, `OPENQUERY`, `OPENROWSET` y `OPENXML`)
Media aritmética (`AVG`)	Usar `COUNT_BIG` y `SUM` como columnas independientes
Funciones de agregado estadístico (`STDEV`, `STDEVP`, `VAR` y `VARP`)
Función `SUM` que hace referencia a una expresión que acepta valores NULL	Usar `ISNULL` dentro de `SUM()` para que la expresión no acepte valores NULL
Otras funciones de agregado (`MIN`, `MAX`, `CHECKSUM_AGG` y `STRING_AGG`)
Funciones de agregado definidas por el usuario (SQL CLR)

Cláusula SELECT	Elemento de Transact-SQL	Posible alternativa
`WITH cte AS`	Expresiones de tabla comunes (CTE) `WITH`
`SELECT`	Subconsultas
`SELECT`	`SELECT [ <table>. ] *`	Asignar nombre a las columnas explícitamente
`SELECT`	`SELECT DISTINCT`	Use `GROUP BY`
`SELECT`	`SELECT TOP`
`SELECT`	Cláusula `OVER`, que incluye funciones de categoría o de agregado
`FROM`	`LEFT OUTER JOIN`
`FROM`	`RIGHT OUTER JOIN`
`FROM`	`FULL OUTER JOIN`
`FROM`	`OUTER APPLY`
`FROM`	`CROSS APPLY`
`FROM`	Expresiones de tabla derivadas (es decir, mediante `SELECT` en la cláusula `FROM`)
`FROM`	Autocombinaciones
`FROM`	Variables de tabla
`FROM`	Función insertada con valores de tabla
`FROM`	Función con valores de tabla de varias instrucciones
`FROM`	`PIVOT`, `UNPIVOT`
`FROM`	`TABLESAMPLE`
`FROM`	`FOR SYSTEM_TIME`	Consulta directamente de la tabla de historial temporal
`WHERE`	Predicados de texto completo (`CONTAINS`, `FREETEXT`, `CONTAINSTABLE`, `FREETEXTTABLE`)
`GROUP BY`	Operadores `CUBE`, `ROLLUP` o `GROUPING SETS`	Definir vistas indizadas independientes para cada combinación de columnas `GROUP BY`
`GROUP BY`	`HAVING`
Operadores de conjuntos	`UNION`, `UNION ALL`, `EXCEPT`, `INTERSECT`	Use `OR`, `AND NOT` y `AND` en la cláusula `WHERE` respectivamente.
`ORDER BY`	`ORDER BY`
`ORDER BY`	`OFFSET`

Tipo de columna de origen	Posible alternativa
Tipos de columna de valores grandes en desuso (text, ntext e image)	Migre columnas a varchar(max), nvarchar(max) y varbinary(max) respectivamente.
columnas xml o FILESTREAM
float ¹ columnas en la clave de índice
Conjuntos de columnas dispersas

¹ La vista indizada puede contener columnas float; sin embargo, estas columnas no se pueden incluir en la clave de índice agrupada.

Importante

No se admiten vistas indexadas con consultas temporales (las consultas que usan la cláusula FOR SYSTEM_TIME).

Recomendaciones de datetime y smalldatetime

Cuando haga referencia a los literales de cadena datetime y smalldatetime de las vistas indizadas, se recomienda convertir explícitamente el literal al tipo de datos deseado mediante un estilo de formato de fecha determinista. Para obtener una lista de los estilos de formato de fecha deterministas, vea CAST y CONVERT. Para obtener más información sobre las expresiones deterministas y no deterministas, consulte la sección Consideraciones de esta página.

Las expresiones que implican la conversión implícita de cadenas de caracteres a datetime o smalldatetime se consideran no deterministas. Para obtener más información, vea Conversión no determinista de las cadenas de fecha literales en valores DATE.

Consideraciones de rendimiento con vistas indexadas

Al ejecutar DML (como UPDATE, DELETE o INSERT) en una tabla a la que hace referencia un gran número de vistas indizadas, o menos vistas indizadas complejas, esas vistas indizadas tienen que actualizarse también durante la ejecución de DML. Como resultado, el rendimiento de la consulta DML se puede degradar notablemente o, en algunos casos, puede que tampoco se genere un plan de consulta. En estos casos, pruebe las consultas DML antes de usarlas en entornos de producción, analice el plan de consulta y ajuste o simplifique la instrucción DML.

Para evitar que el use vistas indizadas, incluya la sugerencia OPTION (EXPAND VIEWS) en la consulta. Además, si alguna de las opciones enumeradas no está establecida correctamente, esta opción impide que el optimizador utilice los índices en las vistas. Para obtener más información sobre la sugerencia OPTION (EXPAND VIEWS), consulte SELECT.

Consideraciones adicionales

La configuración de la opción large_value_types_out_of_row de las columnas de una vista indexada se hereda de la configuración de la columna correspondiente de la tabla base. Este valor se establece mediante sp_tableoption. La configuración predeterminada de las columnas formadas a partir de expresiones es 0. Esto significa que los tipos de valores grandes se almacenan de forma consecutiva.
En una tabla con particiones se pueden crear vistas indizadas, en las que a su vez se pueden crear particiones.
Si se quita la vista, todos sus índices se quitan. Todos los índices no clúster y las estadísticas creadas automáticamente de una vista se quitan si se quita el índice clúster. Las estadísticas creadas por el usuario de la vista se conservan. Los índices no clúster se pueden quitar individualmente. Quitar el índice clúster de la vista quita el conjunto de resultados almacenado; el optimizador vuelve a procesar la vista como una vista estándar.
Los índices de las tablas y las vistas se pueden deshabilitar. Cuando se deshabilita un índice clúster de una tabla, también se deshabilitan los índices de las vistas asociadas a la tabla.

Permisos

Para crear la vista, un usuario necesita el permiso CREATE VIEW en la base de datos y el permiso ALTER en el esquema en el que se crea la vista. Si la tabla base reside en otro esquema, por lo menos se requiere el permiso REFERENCES relativo a la tabla. Si el usuario que crea el índice difiere de los usuarios que crearon la vista, para la creación del índice solo se requiere el permiso ALTER en la vista (cubierto por ALTER en el esquema).

Los índices solo se pueden crear en vistas que tengan el mismo propietario que la tabla o las tablas a las que se hace referencia. Este concepto también se denomina cadena de propiedad intacta entre la vista y las tablas. Normalmente, cuando la tabla y la vista residen en el mismo esquema, el mismo propietario del esquema se aplica a todos los objetos del esquema. Por lo tanto, es posible crear una vista y no ser el propietario de la vista. Por otro lado, también es posible que objetos concretos de un esquema tengan propietarios explícitos diferentes. La columna principal_id de sys.tables contiene un valor si el propietario es diferente del propietario del esquema.

Creación de una vista indexada: un ejemplo de T-SQL

En el ejemplo siguiente se crea una vista y un índice en esa vista, en la base de datos AdventureWorks.

--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;

--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

Las dos consultas siguientes muestran cómo se puede usar la vista indizada, aunque la vista no se especifique en la cláusula FROM.

--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
INNER JOIN Sales.SalesOrderHeader AS o
    ON od.SalesOrderID = o.SalesOrderID
        AND o.OrderDate >= CONVERT(DATETIME, '05/01/2012', 101)
WHERE od.ProductID BETWEEN 700
        AND 800
GROUP BY OrderDate,
    ProductID
ORDER BY Rev DESC;
GO

--This query will also use the above indexed view.
SELECT OrderDate,
    SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
INNER JOIN Sales.SalesOrderHeader AS o
    ON od.SalesOrderID = o.SalesOrderID
        AND o.OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
        AND o.OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
GROUP BY OrderDate
ORDER BY OrderDate ASC;

Por último, en este ejemplo se muestra la consulta directamente desde la vista indizada. Antes de SQL Server 2016 (13.x) Service Pack 1, el uso automático de una vista indexada por parte del optimizador de consultas solo se admite en determinadas ediciones de SQL Server. En la edición SQL Server Standard, debe usar la sugerencia de consulta NOEXPAND para consultar la vista indizada directamente. Desde SQL Server 2016 (13.x) Service Pack 1, todas las ediciones admiten el uso automático de una vista indizada. La base de datos de Azure SQL y Azure SQL Managed Instance también admiten el uso automático de vistas indexadas sin especificar la sugerencia NOEXPAND. Para obtener más información, vea Sugerencias de tabla (Transact-SQL).

--This query uses the indexed view directly, on Enterprise edition.
SELECT OrderDate, Revenue
FROM Sales.vOrders
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
    AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;

--This query uses the indexed view directly, with the NOEXPAND hint.
SELECT OrderDate, Revenue
FROM Sales.vOrders WITH (NOEXPAND)
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
    AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;

Para más información, consulte CREATE VIEW.

Comparteix a través de

Creación de vistas indizadas

Pasos

Opciones SET necesarias para las vistas indizadas

Requisito de vista determinista

Requisitos adicionales

Recomendaciones de datetime y smalldatetime

Consideraciones de rendimiento con vistas indexadas

Consideraciones adicionales

Permisos

Creación de una vista indexada: un ejemplo de T-SQL

Comentaris

Recursos addicionals

Comparteix a través de

Creación de vistas indizadas

Pasos

Opciones SET necesarias para las vistas indizadas

Requisito de vista determinista

Requisitos adicionales

Recomendaciones de datetime y smalldatetime

Consideraciones de rendimiento con vistas indexadas

Consideraciones adicionales

Permisos

Creación de una vista indexada: un ejemplo de T-SQL

Contenido relacionado

Comentaris

Recursos addicionals