Resolver índices de vistas
Al igual que cualquier índice, SQL Server decide utilizar una vista indizada en el plan de consultas cuando el optimizador de consultas considera que es útil.
Las vistas indizadas se pueden crear en cualquier edición de SQL Server. En SQL Server Enterprise, el optimizador de consultas tiene en cuenta de forma automática la vista indizada. Para utilizar una vista indizada en las demás ediciones, debe utilizarse la sugerencia de tabla NOEXPAND.
El optimizador de consultas de SQL Server utiliza una vista indizada cuando se cumplen las siguientes condiciones:
Las siguientes opciones de sesión están establecidas en ON:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
La opción de sesión NUMERIC_ROUNDABORT debe estar establecida en OFF.
El optimizador de consultas encuentra una coincidencia entre las columnas de índice de la vista y los elementos de la consulta, como:
Predicados de condiciones de búsqueda de la cláusula WHERE
Operaciones de combinación
Funciones de agregado
Cláusulas GROUP BY
Referencias a tabla
El costo estimado de utilización del índice es el más bajo de todos los mecanismos de acceso que tiene en cuenta el optimizador de consultas.
Todas las tablas a las que se hace referencia en la consulta (directamente o con la expansión de una vista para tener acceso a sus tablas subyacentes) que se correspondan con una referencia a tabla de la vista indizada deben tener el mismo conjunto de sugerencias de la consulta aplicado en ellas.
[!NOTA]
Las sugerencias READCOMMITTED y READCOMMITTEDLOCK siempre se consideran distintas en este contexto, independientemente del nivel de aislamiento de transacción actual.
Además de los requisitos de las sugerencias de tabla y opciones SET, estas reglas son las mismas que utiliza el optimizador de consultas para determinar si un índice de la tabla satisface una consulta. No ha de especificarse ningún otro elemento para que la consulta utilice una vista indizada.
Una consulta no tiene que hacer referencia explícita a una vista indizada en la cláusula FROM del optimizador de consultas para utilizar la vista indizada. Si la consulta contiene referencias a columnas de las tablas base que también se encuentran en la vista indizada y el optimizador de consultas considera que la vista indizada constituye el mecanismo de acceso de menor costo, el optimizador elige la vista indizada, de forma similar a como elige los índices de la tabla base cuando no se hace referencia directa a los mismos en una consulta. El optimizador de consultas puede elegir la vista en caso de contener columnas a las que no se hace referencia en la consulta, siempre y cuando la vista ofrezca la opción de costo más bajo para incluir una o varias de las columnas especificadas en la consulta.
El optimizador de consultas trata como vista estándar cualquier vista indizada a la que se haga referencia en la cláusula FROM. Expande la definición de la vista en la consulta al inicio del proceso de optimización. A continuación se realiza la coincidencia de vista indizada. Puede que se utilice la vista indizada en el plan de ejecución final seleccionado por el optimizador o que, en su lugar, el plan materialice los datos necesarios de la vista mediante el acceso a las tablas base a las que hace referencia la vista. El optimizador elige la alternativa de menor costo.
Usar sugerencias con vistas indizadas
Puede evitar que los índices de la vista se utilicen en una consulta mediante el uso de la sugerencia de consulta EXPAND VIEWS o utilizar la sugerencia de tabla NOEXPAND para exigir el uso de un índice de una vista indizada especificada en la cláusula FROM de una consulta. Sin embargo, debe dejar que sea el optimizador de consultas el que determine dinámicamente los mejores métodos de acceso para cada consulta. Limite la utilización de EXPAND y NOEXPAND a casos específicos en los que se haya comprobado que el rendimiento mejora significativamente.
La opción EXPAND VIEWS determina que el optimizador de consultas no utilice ninguno de los índices de la vista en toda la consulta.
Cuando se especifica NOEXPAND en una vista, el optimizador de consultas considera la posibilidad de utilizar cualquiera de los índices definidos en la vista. Cuando se especifica NOEXPAND con la cláusula INDEX() opcional, el optimizador de consultas está obligado a utilizar los índices especificados. NOEXPAND sólo se puede especificar en una vista indizada, nunca en una vista no indizada.
Cuando no se especifica NOEXPAND ni EXPAND VIEWS en una consulta que contiene una vista, ésta se expande para tener acceso a las tablas subyacentes. Si la consulta que compone la vista contiene sugerencias de tabla, éstas se propagan a las tablas subyacentes. (Este proceso se explica con más detalle en Resolución de vistas.) Siempre que los conjuntos de resultados que existen en las tablas subyacentes sean idénticos entre sí, se puede seleccionar la consulta para que coincida con una vista indizada. La mayoría de las veces, estas sugerencias coinciden entre sí, porque se han heredado directamente de la vista. Sin embargo, si la consulta hace referencia a tablas en lugar de vistas y las sugerencias aplicadas directamente en estas tablas no son idénticas, no se puede seleccionar dicha consulta para que coincida con una vista indizada. Si las sugerencias INDEX, PAGLOCK, ROWLOCK, TABLOCKX, UPDLOCK o XLOCK se aplican a las tablas a las que hace referencia la consulta después de la expansión de la vista, se puede seleccionar la consulta para la coincidencia de vistas indizadas.
Si una sugerencia de tabla en forma de INDEX (index_val[ ,...n] ) hace referencia a una vista de una consulta y no se especifica también la sugerencia NOEXPAND, se pasa por alto la sugerencia de índice. Para especificar el uso de determinado índice, utilice NOEXPAND.
Por lo general, cuando el optimizador de consultas hace coincidir una vista indizada con una consulta, las sugerencias especificadas en las tablas o vistas de la consulta se aplican directamente a la vista indizada. Si el optimizador de consultas elige no utilizar una vista indizada, las sugerencias se propagan directamente a las tablas a las que se hace referencia en la vista. Para obtener más información, vea Resolución de vistas. Esta propagación no se aplica a las sugerencias de combinación. Sólo se aplican en su posición original en la consulta. El optimizador de consultas no tiene en cuenta las sugerencias de combinación al hacer coincidir consultas con vistas indizadas. Si un plan de consultas utiliza una vista indizada que coincide con parte de una consulta que contiene una sugerencia de consulta, no se utiliza la sugerencia de consulta en el plan.
No se permiten sugerencias en las definiciones de vistas indizadas de SQL Server 2008. En los modos de compatibilidad 80 y superiores, SQL Server pasa por alto las sugerencias incluidas en definiciones de vistas indizadas al mantenerlas o al ejecutar consultas que utilizan vistas indizadas. Aunque utilizar sugerencias en definiciones de vistas indizadas no genera un error de sintaxis en el modo de compatibilidad 80, las sugerencias se pasan por alto.