Règles de conception d'index filtrés

Un index filtré est un index non cluster optimisé, convenant tout particulièrement aux requêtes qui effectuent des sélections dans un sous-ensemble précis de données. Il utilise un prédicat de filtre pour indexer une partie des lignes de la table. Un index filtré bien conçu peut améliorer les performances des requêtes, de même que réduire leurs coûts de maintenance et de stockage par rapport aux index de table entière.

Les index filtrés peuvent présenter les avantages suivants par rapport aux index de table entière :

• Meilleures performances des requêtes et qualité de plan améliorée

  Un index filtré bien conçu améliore les performances des requêtes et la qualité du plan d'exécution car il est plus petit qu'un index non cluster de table entière et contient des statistiques filtrées. Les statistiques filtrées sont plus précises que les statistiques de table entière car elles couvrent uniquement les lignes de l'index filtré.

• Coûts réduits de maintenance des index

  La maintenance d'un index intervient uniquement lorsque les instructions de langage de manipulation de données (DML) affectent les données de l'index. Un index filtré réduit les coûts de maintenance des index par rapport à un index non cluster de table entière car il est plus petit et est demande une maintenance uniquement lorsque les données de l'index sont affectées. Il est possible d'avoir un grand nombre d'index filtrés, notamment s'ils contiennent des données qui sont rarement affectées. De la même façon, si un index filtré contient uniquement les données fréquemment affectées, la plus petite taille de l'index réduit le coût de la mise à jour des statistiques.

• Coûts réduits de stockage des index

  La création d'un index filtré peut réduire le stockage sur disque des index non cluster lorsqu'un index de table entière n'est pas nécessaire. Vous pouvez remplacer un index non cluster de table entière par plusieurs index filtrés sans augmenter considérablement le stockage nécessaire.

Considérations relatives à la conception

Pour concevoir des index filtrés efficaces, il est important de comprendre les requêtes utilisées par votre application et leurs relations avec les sous-ensembles de données. Les colonnes contenant principalement des valeurs NULL, les colonnes contenant des catégories hétérogènes de valeurs et les colonnes contenant des plages de valeurs distinctes sont autant d'exemples de données avec des sous-ensembles bien définis. Les considérations suivantes relatives à la conception présentent divers scénarios dans lesquels un index filtré peut présenter des avantages par rapport à des index de table entière.

Index filtrés pour des sous-ensembles de données

Lorsqu'une colonne contient seulement un petit nombre de valeurs pertinentes pour les requêtes, vous pouvez créer un index filtré sur ce sous-ensemble de valeurs. Ainsi, lorsque les valeurs d'une colonne sont principalement NULL et que la requête effectue uniquement des sélections dans les valeurs non NULL, vous pouvez créer un index filtré pour les lignes de données non NULL. L'index ainsi créé sera plus petit et coûtera moins cher en maintenance qu'un index non cluster de table entière défini sur les mêmes colonnes clés.

Par exemple, la base de données AdventureWorks2008R2 contient une table Production.BillOfMaterials de 2679 lignes. Seules 199 lignes de la colonne EndDate contiennent une valeur non NULL ; les 2480 autres contiennent des valeurs NULL. L'index filtré suivant couvre les requêtes qui retournent les colonnes définies dans l'index et qui sélectionnent uniquement les lignes avec une valeur non NULL pour 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

L'index filtré FIBillOfMaterialsWithEndDate est valide pour la requête suivante. Vous pouvez afficher le plan d'exécution de la requête pour déterminer si l'optimiseur de requête a utilisé l'index filtré. Pour plus d'informations sur l'affichage du plan d'exécution de la requête, consultez Analyse d'une requête.

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

Pour plus d'informations sur la création d'index filtrés et la définition de l'expression de prédicat d'index filtré, consultez CREATE INDEX (Transact-SQL).

Index filtrés pour les données hétérogènes

Lorsqu'une table contient des lignes des données hétérogènes, vous pouvez créer un index filtré pour une ou plusieurs catégories de données.

Par exemple, chacun des produits répertoriés dans la table Production.Product est affecté à un ProductSubcategoryID, qui est à son tour associé à une catégorie de produits (Bikes, Components, Clothing ou Accessories). Ces catégories sont hétérogènes car leurs valeurs de colonne dans la table Production.Product ne sont pas étroitement corrélées. Par exemple, Color, ReorderPoint, ListPrice, Weight, Class et Style ont des caractéristiques uniques pour chaque catégorie de produit. Supposons que des requêtes portent fréquemment sur la catégorie Accessories qui comporte les sous-catégories 27-36. Vous pouvez améliorer les performances des requêtes portant sur Accessories en créant un index filtré sur les sous-catégories de la catégorie Accessories.

L'exemple suivant crée un index filtré sur tous les produits des sous-catégories de la catégorie Accessories dans la table 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

L'index filtré FIProductAccessories couvre la requête suivante car les résultats de la requête

sont contenus dans l'index et le plan de requête n'inclut pas de recherche de table de base. Par exemple, l'expression de prédicat de requête ProductSubcategoryID = 33 est un sous-ensemble du prédicat d'index filtré ProductSubcategoryID >= 27 et ProductSubcategoryID <= 36, les colonnes ProductSubcategoryID et ListPrice dans le prédicat de requête sont toutes deux des colonnes clés dans l'index et le nom est stocké au niveau feuille de l'index en tant que colonne incluse.

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

Vues et index filtrés

Une vue est une table virtuelle qui stocke la définition d'une requête ; sa finalité et ses fonctionnalités sont plus larges que celles d'un index filtré. Pour plus d'informations sur les vues, consultez Présentation des vues et Scénarios d'utilisation des vues. Le tableau suivant compare certaines fonctionnalités autorisées dans les vues avec celles des index filtrés.

Autorisé dans les expressions	Vues	Index filtrés
Colonnes calculées	Oui	Non
Jointures	Oui	Non
Tables multiples	Oui	Non
Logique de comparaison simple dans un prédicat*	Oui	Oui
Logique complexe dans un prédicat**	Oui	Non

*Pour une logique de comparaison simple dans un prédicat, consultez la syntaxe de la clause WHERE dans CREATE INDEX.

**Pour une logique de comparaison complexe dans un prédicat, consultez la syntaxe de la clause WHERE pour SELECT.

Vous ne pouvez pas créer un index filtré sur une vue. Toutefois, l'optimiseur de requête peut tirer parti d'un index filtré défini sur une table référencée dans une vue. L'optimiseur de requête prend en considération un index filtré pour une requête qui effectue des sélections dans une vue si les résultats de la requête sont corrects. L'exemple suivant crée une vue avec des dates de début postérieures au 1er avril 2000 et un index filtré avec des dates de début postérieures au 1er août 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

Dans l'exemple suivant, la requête sélectionne des dates de début postérieures au 1er septembre 2004, lesquelles sont toutes contenues dans l'index filtré et dans la vue filtrée. L'optimiseur de requête prend en considération l'index filtré FIBillOfMaterialsByStartDate car il contient les résultats corrects de la requête.

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

Dans l'exemple suivant, la requête sélectionne les dates de début postérieures au 1er juin 2004, lesquelles sont toutes contenues dans la vue, mais pas dans l'index filtré. L'optimiseur de requête ne prend pas en considération l'index filtré FIBillOfMaterialsByStartDate car la requête peut retourner des résultats différents avec l'index filtré par rapport aux résultats corrects retournés lorsque la requête effectue des sélections dans la vue.

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

Vues indexées et index filtrés

Les index filtrés présentent les avantages suivants par rapport aux vues indexées :

• Coûts réduits de maintenance des index. Par exemple, le processeur de requêtes utilise moins de ressources processeur pour mettre à jour un index filtré qu'une vue indexée.

• Qualité de plan améliorée. Par exemple, lors de la compilation de la requête, l'optimiseur de requête envisage beaucoup plus souvent d'utiliser un index filtré que la vue indexée équivalente.

• Reconstructions d'index en ligne. Vous pouvez reconstruire des index filtrés alors qu'ils sont disponibles pour les requêtes. Les reconstructions d'index en ligne ne sont pas prises en charge pour les vues indexées. Pour plus d'informations, consultez l'option REBUILD pour ALTER INDEX (Transact-SQL).

• Index non uniques. Les index filtrés peuvent être non uniques, alors que les vues indexées doivent être uniques.

Pour les raisons précitées, nous vous recommandons, lorsque cela est possible, d'utiliser un index filtré au lieu d'une vue indexée. Il est possible d'utiliser un index filtré au lieu d'une vue indexée lorsque les conditions suivantes sont réunies : la vue référence une seule table, les requêtes ne retournent pas de colonnes calculées et le prédicat de la vue utilise une logique de comparaison simple. Par exemple, l'expression de prédicat suivante est autorisée dans une définition de vue, mais pas dans des index filtrés car elle contient l'opérateur LIKE.

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

Colonnes clés

Il est recommandé d'inclure un petit nombre de colonnes clés ou incluses dans une définition d'index filtré, et d'incorporer uniquement les colonnes qui sont nécessaires à l'optimiseur de requête pour choisir l'index filtré pour le plan d'exécution de la requête. L'optimiseur de requête peut choisir un index filtré pour la requête, qu'il couvre ou non la requête. Toutefois, l'optimiseur de requête choisira plus probablement un index filtré s'il couvre la requête. Pour plus d'informations sur la couverture de requêtes, consultez Création d'index avec colonnes incluses.

Dans certains cas, un index filtré couvre la requête sans inclure les colonnes de l'expression d'index filtré en tant que colonnes clés ou incluses dans la définition de l'index filtré. Les règles suivantes expliquent dans quels cas une colonne de l'expression d'index filtré doit être une colonne clé ou incluse dans la définition de l'index filtré. Les exemples font référence à l'index filtré FIBillOfMaterialsWithEndDate qui a été créé précédemment.

Il n'est pas nécessaire qu'une colonne de l'expression d'index filtré soit une colonne clé ou incluse dans la définition de l'index filtré si l'expression d'index filtré est équivalente au prédicat de requête et si la requête ne retourne pas la colonne dans l'expression d'index filtré avec les résultats de la requête. Par exemple, l'index filtré FIBillOfMaterialsWithEndDate couvre la requête suivante car le prédicat de la requête est équivalent à l'expression de filtre et EndDate n'est pas retourné avec les résultats de la requête. L'index filtré FIBillOfMaterialsWithEndDate n'a pas besoin de la colonne EndDate comme colonne clé ou incluse dans la définition de l'index filtré.

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

Une colonne de l'expression d'index filtré doit être une colonne clé ou incluse dans la définition de l'index filtré si le prédicat de la requête utilise cette colonne dans une comparaison qui n'est pas équivalente à l'expression d'index filtré. Par exemple, l'index filtré FIBillOfMaterialsWithEndDate est valide pour la requête suivante car il sélectionne un sous-ensemble de lignes dans l'index filtré. Toutefois, il ne couvre pas la requête suivante, car EndDate est utilisé dans la comparaison EndDate > '20040101', qui n'est pas équivalente à l'expression d'index filtré. Le processeur de requête ne peut pas exécuter cette requête sans rechercher les valeurs de EndDate. Par conséquent, EndDate doit être une colonne clé ou incluse dans la définition de l'index filtré.

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

Une colonne de l'expression d'index filtré doit être une colonne clé ou incluse dans la définition de l'index filtré si la colonne se trouve dans le jeu de résultats de la requête. Par exemple, l'index filtré FIBillOfMaterialsWithEndDate ne couvre pas la requête suivante car il retourne la colonne EndDate dans les résultats de la requête. Par conséquent, EndDate doit être une colonne clé ou incluse dans la définition de l'index filtré.

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

Il n'est pas nécessaire que la clé primaire de la table soit une colonne clé ou incluse dans la définition de l'index filtré. La clé primaire est automatiquement incluse dans tous les index non cluster, y compris les index filtrés.

Opérateurs de conversion de données dans le prédicat du filtre

Si l'opérateur de comparaison spécifié dans l'expression d'index filtré de l'index filtré provoque une conversion de données implicite ou explicite, une erreur se produit si cette conversion se produit du côté gauche d'un opérateur de comparaison. Une solution consiste à écrire l'expression d'index filtré avec l'opérateur de conversion de données (CAST ou CONVERT) à droite de l'opérateur de comparaison.

L'exemple suivant crée une table avec différents types de données.

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

Dans la définition d'index filtré suivante, la colonne b est implicitement convertie en type de données integer afin de la comparer à la constante 1. Le message d'erreur 10611 est alors généré car la conversion se produit à gauche de l'opérateur dans le prédicat filtré.

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 solution consiste à convertir la constante qui se trouve à droite de manière à ce que son type soit identique à celui de la colonne b, comme dans l'exemple suivant :

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

Le fait de déplacer la conversion de données de la gauche vers la droite d'un opérateur de comparaison peut modifier la signification de la conversion. Dans l'exemple ci-dessus, lorsque l'opérateur CONVERT a été ajouté à droite, la comparaison de type integer est devenue une comparaison de type varbinary.

Dépendances des références

L'affichage catalogue sys.sql_expression_dependencies suit chaque colonne de l'expression d'index filtré en tant que dépendance de référence. Vous ne pouvez pas supprimer, renommer ni modifier la définition d'une colonne de table définie dans une expression d'index filtré.

Quand utiliser des index filtrés ?

Les index filtrés sont utiles lorsque les colonnes contiennent des sous-ensembles bien définis de données qui sont référencés par des requêtes dans des instructions SELECT. Exemples :

• Colonnes fragmentées qui contiennent uniquement quelques valeurs non NULL.

• Colonnes hétérogènes qui contiennent des catégories de données.

• Colonnes qui contiennent des plages de valeurs, telles que des montants en devise, des heures et des dates.

• Partitions de table définies par une logique de comparaison simple pour les valeurs de colonne.

La réduction des coûts de maintenance pour les index filtrés est plus particulièrement notable lorsque le nombre de lignes de l'index est petit comparé à un index de table entière. Si l'index filtré inclut la plupart des lignes de la table, son coût de maintenance risque d'être plus élevé que celui d'un index de table entière. Dans ce cas, vous devez utiliser un index de table entière à la place d'un index filtré.

Les index filtrés sont définis sur une seule table et ne prennent en charge que les opérateurs de comparaison simples. Si vous avez besoin d'une expression de filtre qui référence plusieurs tables ou présente une logique complexe, vous devez créer une vue.

Prise en charge de la fonctionnalité d'index filtré

En général, le moteur de base de données et les outils prennent en charge les index filtrés de la même manière que les index non cluster de table entière, en considérant les index filtrés comme un type spécial d'index non cluster. La liste suivante indique les outils et les fonctionnalités qui prennent entièrement en charge, ne prennent pas en charge ou prennent en charge de manière limitée les index filtrés.

• ALTER INDEX prend en charge les index filtrés. Pour modifier l'expression d'index filtré, utilisez CREATE INDEX WITH DROP_EXISTING.

• La fonctionnalité d'index manquants ne suggère pas d'index filtrés.

• L'Assistant Paramétrage du moteur de base de données prend en compte les index filtrés lorsqu'il recommande des paramètres d'index et peut recommander un index filtré is not null.

• Les opérations d'index en ligne prennent en charge les index filtrés.

• Les indicateurs de table prennent en charge les index filtrés, mais ont quelques restrictions qui ne s'appliquent pas aux index non filtrés. Celles-ci sont expliquées dans la section suivante.

Considérations relatives aux requêtes

L'optimiseur de requête peut utiliser un index filtré si la requête sélectionne les mêmes résultats en utilisant ou non l'index filtré. L'index filtré FIBillOfMaterialsWithEndDate décrit précédemment est valide pour les deux requêtes suivantes. Dans le premier exemple, le prédicat de la requête est une correspondance exacte avec le prédicat de l'index filtré, WHERE EndDate IS NOT NULL. Dans le deuxième exemple, le prédicat de la requête est plus sélectif que le prédicat du filtre car il contient un sous-ensemble de lignes de l'index.

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

La requête suivante peut également utiliser l'index filtré FIBillOfMaterialsWithEndDate. Toutefois, l'optimiseur peut ne pas sélectionner l'index filtré en raison d'autres facteurs qui déterminent le coût de la requête comme la sélectivité du prédicat de la requête. Vous pouvez forcer l'optimiseur à choisir l'index filtré en l'utilisant comme indicateur de requête, comme illustré dans l'exemple suivant.

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

L'optimiseur de requête n'utilisera pas d'index filtré si la requête peut retourner des lignes qui ne figurent pas dans l'index filtré. Par exemple, l'optimiseur de requête ne prendra pas en considération l'index filtré FIBillOfMaterialsWithEndDate pour la requête suivante car la requête est en mesure de retourner une ligne avec une colonne EndDate NULL et une colonne ModifiedDate non NULL, qui ne peuvent pas être dans l'index filtré FIBillOfMaterialsWithEndDate car ce dernier contient uniquement des valeurs non NULL pour EndDate.

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

Si un index filtré est utilisé explicitement comme indicateur de table et qu'il est susceptible de ne pas contenir tous les résultats de la requête, l'optimiseur de requête génère l'erreur de compilation de requête 8622. Dans l'exemple suivant, l'optimiseur de requête génère l'erreur 8622 car FIBillOfMaterialsWithEndDate n'est pas valide pour la requête et il est utilisé explicitement comme indicateur d'index :

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

Requêtes paramétrées

Dans certains cas, une requête paramétrée ne contient pas suffisamment d'informations à la compilation pour permettre à l'optimiseur de requête de choisir un index filtré. Il est parfois possible de réécrire la requête pour fournir les informations manquantes. Dans l'exemple suivant, l'optimiseur de requête ne prend pas en compte l'index filtré FIBillOfMaterialsWithComponentID pour l'instruction SELECT car les valeurs des paramètres @p et @q ne sont pas connues à la compilation. L'exemple de requête ci-dessous s'exécute avec SHOWPLAN_XML ayant la valeur ON afin que vous puissiez afficher les index filtrés sans correspondance pour les requêtes paramétrées dans la sortie 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

L'élément UnmatchedIndexes et le sous-élément Parameterization de la sortie SHOWPLAN_XML indiquent que l'index filtré n'était pas une correspondance pour la requête. Pour plus d'informations sur l'affichage de la sortie SHOWPLAN_XML, consultez Showplans XML.

La solution consiste à modifier la requête afin que les résultats de la requête soient vides lorsqu'une expression paramétrée n'est pas un sous-ensemble du prédicat du filtre. La requête suivante montre cette modification. L'ajout de l'expression ComponentID in (533, 324, 753) à la clause WHERE garantit que les résultats de la requête sont un sous-ensemble de l'expression de prédicat filtré. Avec cette modification, l'optimiseur de requête peut prendre en considération l'index filtré FIBillOfMaterialsWithComponentID pour l'instruction SELECT suivante.

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

Paramétrage simple

Dans la plupart des cas, l'optimiseur de requête n'effectuera pas de paramétrage simple (connu sous le nom d'auto-paramétrage dans SQL Server 2005) sur une requête si le plan de requête inclut un index filtré. Le paramétrage simple sur de telles requêtes peut élargir la plage de valeurs de paramètre possibles de sorte que l'index filtré ne garantisse pas l'exactitude des résultats de la requête. Par exemple, il se peut que l'optimiseur de requête n'exécute pas un paramétrage simple si la clause WHERE de l'instruction SELECT utilise une colonne qui est utilisée dans le prédicat d'un index filtré, car il est probable que le plan de requête comporte un index filtré.

Le cas échéant, vous pouvez être en mesure de paramétrer la requête en la réécrivant, à l'aide des indications décrites de cette section afin de garantir que l'index filtré couvrira la requête.

Requêtes avec recherches de clés

L'optimiseur de requête peut utiliser un index filtré, même si celui-ci ne couvre pas la requête, en effectuant une recherche de clé pour extraire les colonnes résiduelles que l'index filtré ne couvre pas. Pour plus d'informations sur les recherches de clés, consultez Opérateur Key Lookup Showplan. L'optimiseur de requête peut choisir cette approche si le nombre estimé de recherches de clés est peu élevé. La requête suivante utilise un indicateur d'index pour forcer le processeur de requêtes à utiliser FIBillOfMaterialsWithEndDate avec des recherches de signets pour EndDate. Les recherches de clés interviennent pour la comparaison EndDate > @date dans le prédicat de la requête.

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

Remarquez que EndDate > @Date n'est pas une correspondance exacte avec l'expression d'index filtré EndDate IS NOT NULL. L'index filtré est encore valide pour cette requête paramétrée car elle retourne un sous-ensemble des lignes définies par l'expression d'index filtré.

Voir aussi

Référence

CREATE INDEX (Transact-SQL)

Concepts

Utilisation des statistiques pour améliorer les performances des requêtes