Crear índices filtrados

Se aplica a: síSQL Server (todas las versiones admitidas) SíAzure SQL Database

En este tema se describe cómo crear un índice filtrado en SQL Server mediante SQL Server Management Studio o Transact-SQL. Un índice filtrado es un índice no clúster 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:

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

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

  • Costos reducidos de almacenamiento de índices

    La creación de un índice filtrado puede reducir la cantidad de almacenamiento en disco de índices no clúster, 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.

En este tema

Antes de comenzar

Consideraciones de diseño

  • Cuando una columna solamente tiene un número pequeño de valores pertinentes para las consultas, puede crear un índice filtrado en el subconjunto de valores. Por ejemplo, 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. 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.

  • Cuando una tabla tiene filas de datos heterogéneos, se 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 obtener más información, vea la opción REBUILD de 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 clúster 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.

  • 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 o 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).

Seguridad

Permisos

Requiere el permiso ALTER en la tabla o la vista. El usuario debe 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.

Uso de SQL Server Management Studio

Para crear un índice filtrado

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

  2. Haga clic en el signo más para expandir la carpeta Tablas .

  3. Haga clic en 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. Debajo de Columnas de clave de índice, haga clic en Agregar... .

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

  8. Haga clic en OK.

  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. Haga clic en OK.

Usar Transact-SQL

Para crear un índice filtrado

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

  2. En la barra de Estándar, haga clic en Nueva consulta.

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

    USE AdventureWorks2012;  
    GO  
    -- Looks for an existing filtered index named "FIBillOfMaterialsWithEndDate"  
    -- and deletes it from the table Production.BillOfMaterials if found.   
    IF EXISTS (SELECT name FROM sys.indexes  
        WHERE name = N'FIBillOfMaterialsWithEndDate'  
        AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))  
    DROP INDEX FIBillOfMaterialsWithEndDate  
        ON Production.BillOfMaterials  
    GO  
    -- Creates a filtered index "FIBillOfMaterialsWithEndDate"  
    -- on the table Production.BillOfMaterials   
    -- using the columms ComponentID and StartDate.  
    
    CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate  
        ON Production.BillOfMaterials (ComponentID, StartDate)  
        WHERE EndDate IS NOT NULL ;  
    GO  
    

    El índice filtrado anterior 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 AdventureWorks2012;  
    GO  
    SELECT ProductAssemblyID, ComponentID, StartDate   
    FROM Production.BillOfMaterials  
    WHERE EndDate IS NOT NULL   
        AND ComponentID = 5   
        AND StartDate > '01/01/2008' ;  
    GO  
    

Para asegurarse de que un índice filtrado se usa en una consulta SQL

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

  2. En la barra de Estándar, haga clic en Nueva consulta.

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

    USE AdventureWorks2012;  
    GO  
    SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
        WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )   
    WHERE EndDate IN ('20000825', '20000908', '20000918');   
    GO  
    

Para obtener más información, vea CREATE INDEX(Transact-SQL).