Creación de vistas indizadas

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure 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:

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

Importante

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 indizadas, o menos vistas indizadas muy complejas, esas vistas indizadas a las que se hace referencia también tendrán 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 NULL, aunque 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 Valor requerido 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.
  • Se realiza una operación de inserción, actualización o eliminación en cualquier tabla que participa en la vista indizada. 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 ACTIVAR ACTIVAR ACTIVAR Apagado
ANSI_PADDING ACTIVAR ACTIVAR ACTIVAR Apagado
ANSI_WARNINGS 1 ACTIVAR ACTIVAR ACTIVAR Apagado
ARITHABORT ACTIVAR ACTIVAR Apagado Apagado
CONCAT_NULL_YIELDS_NULL ACTIVAR ACTIVAR ACTIVAR Apagado
NUMERIC_ROUNDABORT Apagado Apagado Apagado Apagado
QUOTED_IDENTIFIER ACTIVAR ACTIVAR ACTIVAR Apagado

1 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, esquema.nombredetabla.

  • 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 Usar 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 Usar 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
    float1 columnas en la clave de índice
    Conjuntos de columnas dispersas

    1 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 que son deterministas, consulte CAST y CONVERT (Transact-SQL). 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 tendrán que actualizarse también durante la ejecución de DML. Como resultado, el rendimiento de las consultas DML puede degradarse significativamente o, en algunos casos, un plan de consulta ni siquiera se puede generar. 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 motor de base de datos use vistas indizadas, incluya la sugerencia OPTION (EXPAND VIEWS) en la consulta. Además, si alguna de las opciones enumeradas no está establecida correctamente, el optimizador no utilizará los índices en las vistas. Para obtener más información sobre la sugerencia OPTION (EXPAND VIEWS), vea SELECT (Transact-SQL).

Varias 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. Esto 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
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
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 (Transact-SQL).

Pasos siguientes