Directrices generales para diseñar índices filtrados

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, reducir los costos de mantenimiento y de almacenamiento del índice en relació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 se ven afectados los datos del índice. Se puede disponer de una gran cantidad de índices filtrados, sobre todo cuando contienen datos que raramente se ven afectados. De igual forma, si un índice filtrado contiene únicamente datos que se ven afectados 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.

Consideraciones de diseño

Para diseñar índices filtrados efectivos, es importante entender qué consultas utiliza la aplicación y cómo se relacionan con los subconjuntos de datos. Algunos ejemplos de datos que tienen subconjuntos bien definidos son las columnas con una mayoría de valores NULL, las columnas con categorías de valores heterogéneas y las columnas con intervalos de valores diferenciados. Las siguientes consideraciones del diseño proporcionan una variedad de escenarios en los que un índice filtrado puede ofrecer ventajas sobre los índices de tabla completa.

Índices filtrados para subconjuntos de datos

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.

Por ejemplo, la base de datos AdventureWorks2008R2 tiene una tabla Production.BillOfMaterials con 2.679 filas. La columna EndDate tiene solamente 199 filas con un valor distinto de NULL, y las otras 2.480 filas contienen valores NULL. El siguiente índice filtrado atenderá consultas que devuelven las columnas definidas en el índice y que seleccionan únicamente filas con un valor distinto de NULL para EndDate.

USE AdventureWorks2008R2;
GO
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
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. Para obtener información acerca de cómo mostrar el plan de ejecución de consultas, vea Analizar una consulta.

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

Para obtener más información sobre cómo crear índices filtrados y cómo definir la expresión de predicado del índice filtrado, vea CREATE INDEX (Transact-SQL).

Índices filtrados para datos heterogéneos

Cuando una tabla tiene filas de datos heterogéneos, se puede crear un índice filtrado para una o varias categorías de datos.

Por ejemplo, cada uno de los productos de la tabla Production.Product está asignado a un ProductSubcategoryID, que a su vez está asociado a las categorías de producto Bikes, Components, Clothing o Accessories. Estas categorías son heterogéneas porque sus valores de columna en la tabla Production.Product no están suficientemente correlacionados. Por ejemplo, Color, ReorderPoint, ListPrice, Weight, Class y Style tienen características únicas para cada categoría de producto. Suponga que se realizan consultas frecuentes para Accessories que tienen las subcategorías 27-36. Puede mejorar el rendimiento de las consultas para Accessories creando un índice filtrado de las subcategorías de Accessories.

En el ejemplo siguiente se crea un índice filtrado para todos los productos de las subcategorías de Accessories de la tabla Production.Product.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIProductAccessories'
    AND object_id = OBJECT_ID ('Production.Product'))
DROP INDEX FIProductAccessories
    ON Production.Product;
GO
CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice) 
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

El índice FIProductAccessories filtrado cubre la consulta siguiente porque los resultados de las consultas

se incluyen en el índice y el plan de consulta no incluye búsquedas en una tabla base. Por ejemplo, la expresión de predicado de la consulta ProductSubcategoryID = 33 es un subconjunto del predicado del índice filtrado ProductSubcategoryID >= 27 y ProductSubcategoryID <= 36, las columnas ProductSubcategoryID y ListPrice del predicado de la consulta son ambas columnas clave del índice, y el nombre se almacena en el nivel hoja del índice como una columna incluida.

SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;
GO

Vistas e índices filtrados

Una vista es una tabla virtual que almacena la definición de una consulta, y tiene una finalidad y una funcionalidad más amplias que un índice filtrado. Para obtener más información sobre las vistas, vea Descripción de vistas y Escenarios de utilización de vistas. En la tabla siguiente se comparan algunas de las funcionalidades permitidas en las vistas con las de los índices filtrados.

Se permite en expresiones	Vistas	Índices filtrados
Columnas calculadas	Sí	No
Combinaciones	Sí	No
Varias tablas	Sí	No
Lógica de comparación simple en un predicado*	Sí	Sí
Lógica compleja en un predicado**	Sí	No

*Para obtener información acerca de la lógica de comparación simple en un predicado, vea la sintaxis de la cláusula WHERE en CREATE INDEX.

**Para obtener información acerca de la lógica de comparación compleja en un predicado, vea la sintaxis de la cláusula WHERE en SELECT.

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. En el ejemplo siguiente se crea una vista con fechas de inicio posteriores al 1 de abril de 2000 y un índice filtrado con fechas de inicio posteriores al 1 de agosto de 2000.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('ViewOnBillOfMaterials') IS NOT NULL
DROP VIEW ViewOnBillOfMaterials;
GO
CREATE VIEW ViewOnBillOfMaterials AS 
SELECT ComponentID, StartDate, EndDate, StartDate + 2 AS ShipDate
FROM Production.BillOfMaterials
WHERE StartDate > '20000401';
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsByStartDate'
    AND object_ID = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsByStartDate 
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsByStartDate
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE StartDate > '20000801';
GO

En el ejemplo siguiente, la consulta selecciona las fechas de inicio posteriores al 1 de septiembre de 2004; todas estas fechas están incluidas en el índice filtrado y en la vista filtrada. El optimizador de consultas tiene en cuenta el índice filtrado FIBillOfMaterialsByStartDate porque contiene los resultados correctos de la consulta.

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040901';
GO

En el ejemplo siguiente, la consulta selecciona fechas de inicio posteriores al 1 de junio de 2004; todas ellas están incluidas en la vista filtrada, pero no en el índice filtrado. El optimizador de consultas no tiene en cuenta el índice filtrado FIBillOfMaterialsByStartDate porque la consulta puede devolver resultados distintos si se utiliza el índice filtrado, en lugar de los resultados correctos si la consulta selecciona en la vista.

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040601';
GO

Vistas indizadas e índices filtrados

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.

Por las razones anteriores, recomendamos el uso de un índice filtrado en lugar de una vista indizada cuando sea posible. Para ello, se tienen que dar las condiciones siguientes: la vista hace referencia solamente a una tabla, las consultas no devuelven columnas calculadas y el predicado de vista utiliza la lógica de comparación simple. Por ejemplo, la expresión de predicado siguiente se admite en una definición de vista pero no en índices filtrados, ya que contiene el operador LIKE.

WHERE StartDate > '20040701' AND ModifiedDate LIKE 'E%'

Columnas de clave

Se recomienda insertar un número pequeño de columnas incluidas o de clave en la definición de un índice filtrado e incorporar solamente las columnas necesarias para que el optimizador de consultas elija el índice filtrado para el plan de ejecución de consultas. El optimizador de consultas puede elegir un índice filtrado para la consulta, independientemente de que cubra la consulta o no. Sin embargo, es más probable que el optimizador de consultas elija un índice filtrado si cubre la consulta. Para obtener más información acerca de las consultas cubiertas, vea Crear índices con columnas incluidas.

En algunos casos, un índice filtrado cubre la consulta sin incluir las columnas en la expresión del índice filtrado como columnas incluidas o de clave en la definición del índice filtrado. Las instrucciones siguientes explican los casos en que una columna de la expresión del índice filtrado debería ser una columna incluida o de clave en la definición del índice filtrado. Los ejemplos hacen referencia al índice filtrado FIBillOfMaterialsWithEndDate que se creó previamente.

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. Por ejemplo, FIBillOfMaterialsWithEndDate cubre la consulta siguiente porque el predicado de consulta es equivalente a la expresión del filtro, y EndDate no se devuelve con los resultados de la consulta. FIBillOfMaterialsWithEndDate no necesita EndDate como una columna incluida o de clave en la definición del índice filtrado.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

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. Por ejemplo, FIBillOfMaterialsWithEndDate es válido para la consulta siguiente porque selecciona un subconjunto de filas del índice filtrado. Sin embargo, no cubre la consulta siguiente porque EndDate se utiliza en la comparación EndDate > '20040101', que no es equivalente a la expresión del índice filtrado. El procesador de consultas no puede ejecutar esta consulta si no busca los valores de EndDate. Por lo tanto, EndDate debe ser una columna incluida o de clave de la definición del índice filtrado.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20040101';
GO

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. Por ejemplo, FIBillOfMaterialsWithEndDate no cubre la consulta siguiente porque devuelve la columna EndDate en los resultados de la consulta. Por lo tanto, EndDate debe ser una columna incluida o de clave de la definición del índice filtrado.

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

La clave principal de la tabla no tiene por qué ser una columna incluida o de clave de la definición del índice filtrado. La clave principal se incluye de forma automática en todos los índices no clúster, incluidos los índices filtrados.

Operadores de conversión de datos en el predicado de filtro

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.

En el ejemplo siguiente se crea una tabla con varios tipos de datos.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.TestTable') IS NOT NULL
DROP TABLE dbo.TestTable;
GO
CREATE TABLE TestTable (a int, b varbinary(4));
GO

En la siguiente definición del índice filtrado, la columna b se convierte implícitamente en un tipo de datos enteros para que se pueda comparar con la constante 1. Esto genera el mensaje de error 10611 porque la conversión se produce en el lado izquierdo del operador del predicado filtrado.

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name from sys.indexes 
    WHERE name = N'TestTabIndex'
    AND object_id = OBJECT_ID (N'dbo.TestTable'))
DROP INDEX TestTabIndex on dbo.TestTable
GO
CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO

La solución consiste en convertir la constante del lado derecho de forma que sea del mismo tipo que la columna b, tal como se muestra en el ejemplo siguiente:

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO

Cuando se mueve la conversión de datos del lado izquierdo al lado derecho de un operador de comparación, es posible que cambie el significado de la conversión. En el ejemplo anterior, cuando se agregó el operador CONVERT al lado derecho, la comparación cambió de una comparación de enteros a una comparación varbinary.

Dependencias de referencia

La vista de catálogo sys.sql_expression_dependencies realiza el seguimiento de cada columna de la expresión del índice filtrado como una dependencia de referencia. No se puede quitar, modificar o cambiar el nombre de la definición de una columna de la tabla que se define en una expresión del índice filtrado.

Casos en los que se utilizan los índices filtrados

Los índices filtrados son útiles cuando las columnas contienen subconjuntos de datos bien definidos a los que las consultas hacen referencia en instrucciones SELECT. Algunos ejemplos son:

• Columnas dispersas que contienen solamente unos pocos valores distintos de NULL.

• Columnas heterogéneas que contienen categorías de datos.

• Columnas que contienen intervalos de valores como cantidad de dinero, hora y fechas.

• Particiones de tablas definidas por lógica de comparación simple para los valores de las columnas.

La reducción de los costos de mantenimiento para los índices filtrados es más apreciable cuando el número de filas del índice es pequeño en relación con un índice de tabla completa. Si el índice filtrado incluye la mayoría de las filas en la tabla, puede resultar más costoso mantenerlo que un índice de tabla completa. En ese caso, debe utilizar un índice de tabla completa en lugar de un índice filtrado.

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.

Compatibilidad de las características con los índices filtrados

En general, Motor de base de datos y las herramientas proporcionan la misma compatibilidad con los índices filtrados que para los índices no clúster de tabla completa, y consideran a los índices filtrados como un tipo especial de índices no clúster. La lista siguiente contiene notas sobre las herramientas y características que son total o parcialmente compatibles, o que son incompatibles, con los índices filtrados.

• ALTER INDEX admite los índices filtrados. Para modificar la expresión de índice filtrado, utilice CREATE INDEX WITH DROP_EXISTING.

• La característica de índices que faltan no sugiere índices filtrados.

• El Asistente para la optimización de Motor de base de datos tiene en cuenta los índices filtrados al recomendar la optimización de índices y puede recomendar un índice filtrado is not null.

• Las operaciones de índices en línea admiten índices filtrados.

• Las sugerencias de tabla admiten índices filtrados, pero presentan algunas restricciones que no se aplican a los índices no filtrados. Estas restricciones se explican en la sección siguiente.

Consideraciones sobre las consultas

El optimizador de consultas puede utilizar un índice filtrado si la consulta selecciona los mismos resultados usando o no el índice filtrado. El índice filtrado FIBillOfMaterialsWithEndDate descrito previamente es válido para las dos consultas siguientes. En el primer ejemplo, el predicado de consulta es una coincidencia exacta con el predicado del índice filtrado, WHERE EndDate IS NOT NULL. En el segundo ejemplo, el predicado de consulta es más selectivo que el predicado del filtro porque contiene un subconjunto de filas en el índice.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate < '20040701';
GO

La consulta siguiente también puede utilizar FIBillOfMaterialsWithEndDate. Sin embargo, es posible que el optimizador no seleccione el índice filtrado como consecuencia de otros factores que determinan el costo de la consulta, tales como la selectividad del predicado de consulta. Puede hacer que el optimizador elija el índice filtrado utilizándolo como una sugerencia de consulta, tal como se muestra en el ejemplo siguiente.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
    WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )
WHERE EndDate IN ('20040825', '20040908', '20040918');
GO

El optimizador de consultas no utilizará un índice filtrado si la consulta puede devolver filas que no están en el índice filtrado. Por ejemplo, el optimizador de consultas no tendrá en cuenta FIBillOfMaterialsWithEndDate para la consulta siguiente, porque es probable que la consulta devuelva una fila con un valor NULL en EndDate y un valor distinto de NULL en ModifiedDate, y éste no puede estar en FIBillOfMaterialsWithEndDate porque solamente contiene valores distintos de NULL para EndDate.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL OR ModifiedDate IS NOT NULL;
GO

Si un índice filtrado se utiliza de forma explícita como una sugerencia de tabla y suponiendo que el índice filtrado pudiese contener todos los resultados de la consulta, el optimizador de consultas generará el error de compilación de consulta 8622. En el ejemplo siguiente, el optimizador de consultas genera el error 8622 porque FIBillOfMaterialsWithEndDate no es válido para la consulta y se utiliza de forma explícita como una sugerencia del índice:

SELECT StartDate, ComponentID FROM Production.BillOfMaterials
    WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )
WHERE EndDate IS NOT NULL OR ModifiedDate IS NOT NULL;
GO

Consultas parametrizadas

En algunos casos, una consulta parametrizada no contiene información suficiente en tiempo de compilación para que el optimizador de consultas elija un índice filtrado. Es posible que se pueda volver a escribir la consulta para proporcionar la información que falta. En el ejemplo siguiente, el optimizador de consultas no tiene en cuenta el índice filtrado FIBillOfMaterialsWithComponentID para la instrucción SELECT porque en tiempo de compilación no se conocen los valores de parámetro para @p y @q. El siguiente ejemplo de consulta se ejecuta con SHOWPLAN_XML activado para que se puedan ver los índices filtrados no coincidentes para las consultas parametrizadas en el resultado de SHOWPLAN_XML.

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE ComponentID IN (533, 324, 753);
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID from Production.BillOfMaterials 
WHERE ComponentID = @p OR ComponentID = @q;
GO
SET SHOWPLAN_XML OFF;
GO

El elemento UnmatchedIndexes y el subelemento Parameterization del resultado de SHOWPLAN_XML indican que el índice filtrado no fue una de las coincidencias de la consulta. Para obtener información acerca de cómo mostrar el resultado de SHOWPLAN_XML, vea Planes de presentación XML.

La solución consiste en modificar la consulta para que sus resultados estén vacíos cuando una expresión parametrizada no sea un subconjunto del predicado de filtro. En la consulta siguiente se muestra esta modificación. Agregando la expresión ComponentID in (533, 324, 753) a la cláusula WHERE, se garantiza que los resultados de la consulta sean un subconjunto de la expresión de predicado filtrada. Con esta modificación, el optimizador de consultas puede tener en cuenta el índice filtrado FIBillOfMaterialsWithComponentID para la siguiente instrucción SELECT.

USE AdventureWorks2008R2;
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WHERE ComponentID in (533, 324, 753)
    AND (ComponentID = @p OR ComponentID = @q);
GO
SET SHOWPLAN_XML OFF;
GO

Parametrización simple

En la mayoría de los casos, el optimizador de consultas no realizará la parametrización simple (denominada parametrización automática en SQL Server 2005) en una consulta si el plan de consulta incluye un índice filtrado. El uso de la parametrización simple en este tipo de consulta puede ampliar el intervalo de valores posibles de parámetro hasta el punto de que el índice filtrado no garantice la precisión de los resultados de la consulta. Por ejemplo, el optimizador de consultas no puede realizar la parametrización simple si la cláusula WHERE de la instrucción SELECT usa una columna que se utiliza en el predicado de un índice filtrado, ya que es probable que el plan de consulta incluya un índice filtrado.

Cuando proceda, podrá parametrizar la consulta volviéndola a escribir; para ello, siga las instrucciones descritas en esta sección para asegurarse de que el índice filtrado cubrirá la consulta.

Consultas con búsquedas de claves

El optimizador de consultas puede usar un índice filtrado incluso en el caso de que éste no cubra la consulta; para ello, realiza una búsqueda de claves con objeto de recuperar las columnas residuales que el índice filtrado no cubre. Para obtener más información acerca de las búsquedas de claves, vea Operador de plan de presentación de búsqueda de claves. Es posible que el optimizador de consultas elija este enfoque si el número estimado de búsquedas de claves es pequeño. En la consulta siguiente se utiliza una sugerencia de índice para forzar que el procesador de consultas utilice FIBillOfMaterialsWithEndDate con búsquedas de marcadores para EndDate. La búsqueda de claves se produce para la comparación EndDate > @date en el predicado de la consulta.

USE AdventureWorks2008R2;
GO
DECLARE @date AS DATE;
SET @date = '20040825'
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WITH ( INDEX (FIBillOfMaterialsWithEndDate) )
WHERE EndDate > @date;
GO

Observe que EndDate > @Date no es una coincidencia exacta de la expresión EndDate IS NOT NULL de índice filtrado. El índice filtrado todavía es válido para esta consulta parametrizada porque devuelve un subconjunto de las filas definido por la expresión de índice filtrado.

Vea también

Referencia

CREATE INDEX (Transact-SQL)

Conceptos

Utilizar las estadísticas para mejorar el rendimiento de las consultas