Indices filtrados con SQL Server

Índices filtrados

Esta característica está disponible a partir de SQL Server 2008 y posteriores. Los índices de filtro o filtrados son la característica más poderosa de SQL, ya que proporciona oportunidades para un uso mucho más eficiente de las búsquedas y tiene un gran potencial.

Un índice filtrado es un índice no agrupado (non clustered) optimizado que nos permite definir filtro con la cláusula WHERE siempre que creamos el índice. Un índice filtrado bien diseñado puede mejorar el rendimiento de las consultas, reducir los costos de mantenimiento de índices, y reducir los costos de almacenamiento de cada índice en comparación con los índices de tabla completa.

Los índices filtrados permiten crear un índice en un subconjunto de datos utilizando un predicado filtrado.

Un índice no agrupado optimizado ofrece varias ventajas sobre una tabla indexada llena con un índice no agrupado.

Mejora de rendimiento de las consultas y de la calidad del plan

Un índice filtrado bien diseñado mejora el rendimiento de la consulta y la calidad del plan de ejecución, pues es más pequeño que un índice no agrupado. Las estadísticas filtradas son más precisas que las de tabla completa debido a que cubren sólo las filas en el índice filtrado.

Reduce los costos de mantenimiento del índice

Un índice filtrado reduce los costes de mantenimiento en comparación con el índice no agrupado porque es más pequeño y sólo se mantiene cuando hay cambios en los datos del índice.

Reduce los costos de almacenamiento del índice

El uso de un índice filtrado reduce el espacio utilizado para almacenamiento en disco para índices no agrupados. Podemos reemplazar un índice no agrupado con múltiples índices filtrados sin aumentar significativamente los requisitos de almacenamiento.

Consideraciones de diseño

Cuando un campo sólo tiene un pequeño número de valores relevantes para las consultas, podemos crear un índice filtrado en el subconjunto de valores.

Si una tabla tiene filas de datos heterogéneas, podemos crear un índice filtrado para una o más categorías de datos. Esto puede mejorar el rendimiento de las consultas sobre estas filas de datos estrechando el foco de una consulta a un área específica de la tabla.

Una vez más, el índice resultante será más pequeño y cuestan menos de mantener que un índice no agrupado de tabla completa.

Sintaxis del índice filtrado y Rendimiento

Para ver el rendimiento del índice filtrado, utilizaremos una tabla creada por nosotros en la base de datos model. En el final del artículo de este enlace está el script de creación de la tabla. 

USE [model]

GO

SELECT * FROM tbAtiende

WHERE strID_MED = '001'

Una vez escrita la sentencia podemos consultar el plan de ejecución sobre la opción del menú Consulta -> Mostrar plan de ejecución estimado.

Esto nos muestra el plan de ejecución.

Ahora, vamos a crear un índice filtrado sobre la tabla y ejecutaremos las consultas, como se muestra a continuación:

USE [model]

GO

CREATE NONCLUSTERED INDEX Idx_tbAtiende_intID

ON

model.dbo.tbAtiende (intID)

WHERE

strID_MED = '001'

Después de crear el índice filtrado, el recorrido de índice es del 50% sobre el índice agrupado y un 50% en agrupado Índice continuación, costo de la consulta es de 50%, como se muestra a continuación:

Diferencia entre los índices filtrados y las vistas indexadas

Los índices filtrados tienen las siguientes ventajas sobre las vistas  indexadas

Criterio	Índices filtrados	Vistas indexadas
Costes de mantenimiento	Costes de mantenimiento reducidos debido a que el procesador de consultas utiliza menos recursos de CPU para actualizar un índice filtrado	El procesador de consultas utiliza más recursos de CPU para actualizar una vista indexada.
Plan de calidad	plan de calidad mejorado debido a que durante compilación de la consulta, el optimizador de consultas tiene en cuenta el uso de un índice filtrado en más situaciones	Estas no son tan beneficiosas en el optimizador de consultas como los índices filtrados
Reconstrucciones online del índice	Los índices filtrados que están disponibles para consultas	Mientras se reconstruye la vista indexada, no está disponible
Índices no exclusivos	Los índices filtrados no pueden ser no exclusivos	Las vistas indexadas deben ser exclusivas
Sólo una tabla	Un índice filtrado se crea en columnas de una tabla en particular	Vistas indexadas  se pueden Crear en Columnas de varias tablas
Criterio WHERE simple	Un índice filtrado no puede usar la lógica compleja en su cláusula WHERE, por ejemplo, las cláusulas LIKE, NOT IN, OR y predicados dinámicos / no deterministas como WHERE col> = DATEADD (DAY, -1, GETDATE ()) no están permitidos, sólo se permiten los operadores de comparación simples.	Esta limitación no se aplica a las vistas indexadas y se pueden diseñar sus criterios tan complejos como desee.

Limitaciones y restricciones

Los índices filtrados son muy útiles cuando un procedimiento almacenado debe seleccionar de forma rutinaria un tipo específico de conjunto de resultados de una gran línea vertical (Entidad-Atributo-Valor). Con un índice tradicional no filtrado, seleccionar el tipo de datos único de una tabla EAV puede resultar tedioso.

No se puede crear un índice filtrado en una vista. Sin embargo, el optimizador de consultas puede beneficiarse de un índice filtrado definido en una tabla que se hace referencia en una vista.

Los índices filtrados tienen las siguientes ventajas con respecto a las vistas indexadas:

Una columna en una expresión de índice filtrado no necesita ser una columna de clave o estar incluida en la definición del índice filtrado si la expresión índice filtrado es equivalente al predicado de la consulta y la consulta no devuelve la columna en la expresión índice filtrado con los resultados de la consulta.

Una columna en una expresión de índice filtrado debe ser una columna de clave o estar  incluida en la definición del índice filtrado si el predicado de la consulta utiliza la columna en una comparación que no es equivalente a la expresión del índice filtrado.

Una columna de la expresión índice filtrado debe ser una columna de clave o estar incluida en la definición de índice filtrado si la columna está en el conjunto de resultados de consulta.

La clave del índice agrupado de la tabla no tiene por qué ser una clave o columna incluida en la definición índice filtrado. La clave del índice agrupado se incluye automáticamente en todos los índices no agrupados, incluyendo índices filtrados.

Si el operador de comparación especificado en la expresión índice filtrado de los resultados del índice filtrado en una conversión de datos implícita o explícita, se producirá un error si la conversión se produce en el lado izquierdo de un operador de comparación. Una solución es escribir la expresión índice filtrado con el operador de conversión de datos (CAST o CONVERT) en el lado derecho del operador de comparación.

Basado en filtered indexes