Creación de índices filtrados

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

En este artículo se describe cómo crear un índice filtrado mediante SQL Server usando SQL Server Management Studio (SSMS) o Transact-SQL. Un índice filtrado es un índice no agrupado basado en disco y optimizado, especialmente indicado para atender consultas que realizan selecciones a partir un subconjunto bien definido de datos. Utiliza un predicado de filtro para indizar una parte de las filas de la tabla. Un índice filtrado bien diseñado puede mejorar el rendimiento de las consultas, así como reducir los costos de almacenamiento y mantenimiento del índice en comparación con los índices de tabla completa.

Los índices filtrados pueden proporcionar las siguientes ventajas respecto a los índices de tabla completa:

1. Mejor rendimiento de las consultas y mayor calidad del plan.

   Un índice filtrado bien diseñado mejora el rendimiento de las consultas y la calidad del plan de ejecución porque es menor que un índice no clúster de tabla completa y tiene estadísticas filtradas. Las estadísticas filtradas son más precisas que las de tabla completa porque corresponden solamente a las filas del índice filtrado.

2. Menor costo de mantenimiento de índices.

   El mantenimiento de un índice se realiza únicamente cuando las instrucciones de lenguaje de manipulación de datos (DML) afectan a los datos en el índice. Un índice filtrado reduce los costos de mantenimiento del índice en comparación con un índice no clúster de tabla completa, ya que es menor y el mantenimiento se realiza únicamente cuando cambian los datos del índice. Se puede disponer de una gran cantidad de índices filtrados, sobre todo cuando contienen datos que cambian con poca frecuencia. De igual forma, si un índice filtrado contiene únicamente datos que se modifican a menudo, el tamaño menor del índice reduce el costo de actualización de las estadísticas.

3. Costos reducidos de almacenamiento de índices.

   Creación de un índice filtrado puede reducir la cantidad de almacenamiento en disco de índices no agrupado, cuando no sea necesario un índice de tabla completa. Puede reemplazar un índice no clúster de tabla completa con varios índices filtrados sin aumentar de forma considerable los requisitos de almacenamiento.

Consideraciones de diseño

Cuando una columna solamente tiene pocos valores pertinentes para las consultas, puede crear un índice filtrado en el subconjunto de valores. El índice resultante será menor y tendrá costos de mantenimiento más reducidos que los de un índice no clúster de tabla completa definido en las mismas columnas de clave.

Por ejemplo, considere un índice filtrado en los escenarios de datos a continuación. En cada caso, la cláusula WHERE de una consulta tiene que ser un subconjunto de la cláusula WHERE del índice filtrado de para beneficiarse del índice filtrado.

• Cuando los valores en una columna son principalmente NULL y la consulta solamente selecciona entre valores distintos de NULL. Puede crear un índice filtrado para las filas de datos distintos de NULL.
• Cuando las filas de una tabla se marcan como procesadas por un proceso periódico de flujo de trabajo o cola. Con el tiempo, la mayoría de las filas de la tabla se marcarán como procesadas. Un índice filtrado en filas que aún no se han procesado beneficiaría a la consulta periódica que busca filas que aún no se han procesado.
• Cuando una tabla tiene filas de datos heterogéneos. Puede crear un índice filtrado para una o varias categorías de datos. Esto puede mejorar el rendimiento de las consultas de estas filas de datos estrechando el foco de una consulta a un área concreta de la tabla. También en este caso, el índice resultante será menor y tendrá costos de mantenimiento más reducidos que los de un índice no clúster de tabla completa.

Limitaciones y restricciones

• No se puede crear un índice filtrado en una vista. Sin embargo, el optimizador de consultas puede aprovechar un índice filtrado definido en una tabla a la que se hace referencia en una vista. El optimizador de consultas tiene en cuenta un índice filtrado para una consulta que selecciona en una vista si los resultados de la consulta serán correctos.

• No se puede crear un índice filtrado en una tabla cuando la columna a la que se obtiene acceso en la expresión de filtro es de un tipo de datos CLR.

• Los índices filtrados presentan las ventajas siguientes con respecto a las vistas indizadas:

  • Menor costo de mantenimiento de índices. Por ejemplo, el procesador de consultas utiliza menos recursos de CPU para actualizar un índice filtrado que una vista indizada.

  • Calidad del plan mejorada. Por ejemplo, durante la compilación de la consulta, el optimizador de consultas considera la posibilidad de usar un índice filtrado en más situaciones que la vista indizada equivalente.

  • El índice en línea se vuelve a generar. Puede volver a generar los índices filtrados mientras están disponibles para las consultas. Esto no es posible en el caso de las vistas indizadas. Para más información, consulte la opción REBUILD para ALTER INDEX (Transact-SQL).

  • Índices no únicos. Los índices filtrados pueden ser no únicos, mientras que las vistas indizadas deben ser únicas.

• Los índices filtrados se definen en una tabla y solamente admiten operadores de comparación simples. Cuando necesite una expresión de filtro que haga referencia a varias tablas o que tenga lógica compleja, deberá crear una vista. Los índices filtrados no admiten operadores LIKE.

• Una columna de la expresión del índice filtrado no tiene por qué ser una columna incluida o de clave en la definición del índice filtrado cuando la expresión del índice filtrado es equivalente al predicado de la consulta y la consulta no devuelve la columna de la expresión del índice filtrado con los resultados de la consulta.

• Una columna de la expresión del índice filtrado debe ser una columna incluida o de clave de la definición del índice filtrado cuando el predicado de la consulta usa la columna en una comparación no equivalente a la expresión del índice filtrado.

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

• La clave de índice agrupado de la tabla no tiene por qué ser una columna incluida o de clave de la definición del índice filtrado. La clave de índice cluster se incluye de forma automática en todos los índices no clúster, incluidos los índices filtrados. Obtenga más información en la guía de diseño y arquitectura de índices.

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

• Revise las opciones SET necesarias para la creación del índice filtrado en la sintaxis CREATE INDEX (Transact-SQL)

• Los filtros no se pueden aplicar a restricciones de clave principal o únicas, pero se pueden aplicar a índices con la propiedad UNIQUE.

• No se puede crear un índice filtrado en una columna calculada.

Permisos

Requiere el permiso ALTER en la tabla o la vista. El usuario tiene que ser miembro del rol fijo de servidor sysadmin o de los roles fijos de base de datos db_ddladmin y db_owner. Para modificar la expresión de índice filtrado, utilice CREATE INDEX WITH DROP_EXISTING.

Creación de un índice filtrado con SSMS

1. En el Explorador de objetos, seleccione el signo más para expandir la base de datos que contiene la tabla en la que desea crear un índice filtrado.

2. Selecciona el signo más para expandir la carpeta Tablas.

3. Seleccione el signo más para expandir la tabla en la que desea crear un índice filtrado.

4. Haga clic con el botón derecho en la carpeta Índices, seleccione Nuevo índice y, luego, Índice no agrupado....

5. En el cuadro de diálogo Nuevo índice , en la página General , escriba el nombre del nuevo índice en el cuadro Nombre de índice .

6. En Columnas de clave de índice, seleccione Agregar....

7. En el cuadro de diálogo Seleccionar columnas de table_name, active la o las casillas de la o las columnas de tabla que se van a agregar al índice.

8. Seleccione Aceptar.

9. En la página Filtro, debajo de Expresión de filtro, escriba la expresión SQL que usará para crear el índice filtrado.

10. Seleccione Aceptar.

Creación de un índice filtrado con Transact-SQL

Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022 o AdventureWorksDW2022, que se pueden descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.

1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.

2. En la barra Estándar, seleccione Nueva consulta.

3. Copie y pegue el ejemplo siguiente en la ventana de consulta y seleccione Ejecutar.

USE AdventureWorks2022;
GO

DROP INDEX IF EXISTS FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

El índice filtrado FIBillOfMaterialsWithEndDate es válido para la consulta siguiente. Puede mostrar el plan de ejecución de consultas para determinar si el optimizador de consultas ha utilizado el índice filtrado.

USE AdventureWorks2022;
GO

SELECT ProductAssemblyID, ComponentID, StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
    AND ComponentID = 5
    AND StartDate > '01/01/2008' ;
GO

Pasos siguientes

Para obtener más información acerca de la creación de índices y conceptos relacionados, consulte los siguientes artículos:

• CREATE INDEX (Transact-SQL)
• Guía de diseño y de arquitectura de índices de SQL Server y Azure SQL
• Mostrar un plan de ejecución real
• Tipos de índice