Règles de conception d'index filtrés

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 AdventureWorks 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 AdventureWorks;
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).

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 AdventureWorks;
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

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.

*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 AdventureWorks;
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 2000, 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 > '20000901';
GO

Dans l'exemple suivant, la requête sélectionne les dates de début postérieures au 1er juin 2000, 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 > '20000601';
GO

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 > '20000701' AND ModifiedDate LIKE 'E%'

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 > '20000101', 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 > '20000101';
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.

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 AdventureWorks;
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 AdventureWorks;
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.

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

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.

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.

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 AdventureWorks;
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 AdventureWorks;
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

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 AdventureWorks;
GO
DECLARE @date AS DATE;
SET @date = '20000825'
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é.