Guide de conception d'index SQL ServerSQL Server Index Design Guide

L'engorgement des applications de base de données est souvent imputable à des index mal conçus ou en nombre insuffisant.Poorly designed indexes and a lack of indexes are primary sources of database application bottlenecks. La conception d'index efficaces est primordiale pour le bon fonctionnement des bases de données et des applications.Designing efficient indexes is paramount to achieving good database and application performance. Ce guide de conception d'index SQL Server contient les informations et les meilleures pratiques nécessaires à la création d'index efficaces pour répondre aux besoins de votre application.This SQL Server index design guide contains information and best practices to help you design effective indexes to meet the needs of your application.

S'applique à: SQL Server 2005SQL Server 2005 et SQL Server 2014SQL Server 2014 , sauf indication contraire.Applies to: SQL Server 2005SQL Server 2005 through SQL Server 2014SQL Server 2014 unless noted otherwise.

Ce guide suppose que le lecteur connaît les types d'index disponibles dans SQL ServerSQL Server.This guide assumes the reader has a general understanding of the index types available in SQL ServerSQL Server. Pour obtenir description générale des types d'index, consultez Types d'index.For a general description of index types, see Index Types.

Dans ce GuideIn This Guide

Principes fondamentaux de conception de indexIndex Design Basics

Instructions de conception d’Index généralGeneral Index Design Guidelines

Indications pour la conception d’index clusterClustered Index Design Guidelines

Indications pour la conception d’index non-clusterNonclustered Index Design Guidelines

Indications pour la conception d’index uniquesUnique Index Design Guidelines

Indications pour la conception d’index filtrésFiltered Index Design Guidelines

Lecture supplémentaireAdditional Reading

Notions de base de la conception d'indexIndex Design Basics

Un index est une structure sur disque associée à une table ou une vue qui accélère l'extraction des lignes de la table ou de la vue.An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. Il contient des clés créées à partir d'une ou plusieurs colonnes de la table ou de la vue.An index contains keys built from one or more columns in the table or view. Ces clés sont stockées dans une structure (B-tree) qui permet à SQL Server de trouver rapidement et efficacement la ou les lignes associées aux valeurs de clé.These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

Le choix d'index adaptés à une base de données et à sa charge de travail est une opération complexe qui vise à trouver un compromis entre vitesse des requêtes et coûts de mise à jour.The selection of the right indexes for a database and its workload is a complex balancing act between query speed and update cost. Les index étroits, c'est-à-dire les index ne comportant que quelques colonnes dans la clé d'index, requièrent moins d'espace disque et de besoins de maintenance.Narrow indexes, or indexes with few columns in the index key, require less disk space and maintenance overhead. En revanche, les index larges couvrent plus de requêtes.Wide indexes, on the other hand, cover more queries. Vous devrez éventuellement essayer plusieurs conceptions différentes avant de trouver l'index le plus performant.You may have to experiment with several different designs before finding the most efficient index. Il est possible d'ajouter, de modifier et de supprimer des index sans affecter le schéma de la base de données ou la conception des applications.Indexes can be added, modified, and dropped without affecting the database schema or application design. Par conséquent, n'hésitez à faire des essais avec différents index.Therefore, you should not hesitate to experiment with different indexes.

Dans la majorité des cas, l'optimiseur de requête de SQL ServerSQL Server choisit de manière fiable l'index le plus efficace.The query optimizer in SQL ServerSQL Server reliably chooses the most effective index in the vast majority of cases. La stratégie globale de création d'index consiste à fournir à l'optimiseur de requête une sélection variée d'index et à se fier à lui pour faire le bon choix.Your overall index design strategy should provide a variety of indexes for the query optimizer to choose from and trust it to make the right decision. Ce procédé permet de réduire le temps d'analyse et produit de bons résultats dans bon nombre de cas.This reduces analysis time and produces good performance over a variety of situations. Pour déterminer quels sont les index qu'utilise l'optimiseur de requête dans le cas d'une requête donnée, sélectionnez SQL Server Management StudioSQL Server Management StudioInclure le plan d'exécution réel dans le menu Requête de.To see which indexes the query optimizer uses for a specific query, in SQL Server Management StudioSQL Server Management Studio, on the Query menu, select Include Actual Execution Plan.

L'utilisation d'index n'est pas forcément synonyme de bonnes performances, et inversement, de bonnes performances ne sauraient être nécessairement attribuables à l'utilisation d'index efficaces.Do not always equate index usage with good performance, and good performance with efficient index use. Si l'utilisation d'un index contribuait toujours à produire les meilleurs résultats, le travail de l'optimiseur de requête en serait simplifié.If using an index always helped produce the best performance, the job of the query optimizer would be simple. En réalité, le choix d'un index inapproprié peut aboutir à des performances moins que satisfaisantes.In reality, an incorrect index choice can cause less than optimal performance. La tâche de l'optimiseur de requête est donc de ne sélectionner un index, ou une combinaison d'index, que dans les cas où cette sélection est susceptible d'améliorer les performances et d'éviter la récupération par index si elle doit les détériorer.Therefore, the task of the query optimizer is to select an index, or combination of indexes, only when it will improve performance, and to avoid indexed retrieval when it will hinder performance.

Tâches de conception d'indexIndex Design Tasks

La stratégie de conception d'index que nous recommandons est constituée des tâches suivantes :The follow tasks make up our recommended strategy for designing indexes:

  1. Comprendre les caractéristiques de la base de données elle-même.Understand the characteristics of the database itself. Par exemple, s'agit-il d'une base de données de traitement transactionnel en ligne (OLTP) dont les données sont souvent modifiées, ou d'une base de données d'aide à la décision (DSS) ou d'entreposage de données (OLAP) contenant essentiellement des données en lecture seule et devant traiter des jeux de données volumineux rapidement ?For example, is it an online transaction processing (OLTP) database with frequent data modifications, or a Decision Support System (DSS) or data warehousing (OLAP) database that contains primarily read-only data and must process very large data sets quickly. Dans SQL Server 2012SQL Server 2012, les index columnstore optimisés en mémoire xVelocity sont particulièrement adaptés aux jeux de données d'entrepôts de données classiques.In SQL Server 2012SQL Server 2012, xVelocity memory optimized columnstore index is especially appropriate for typical data warehousing data sets. Les index columnstore peuvent transformer l'expérience utilisateur des entrepôts de données en améliorant considérablement les performances des requêtes communes liées aux entrepôts de données, par exemple en matière de filtrage, d'agrégation, de regroupement et de jointure en étoile.Columnstore indexes can transform the data warehousing experience for users by enabling faster performance for common data warehousing queries such as filtering, aggregating, grouping, and star-join queries. Pour plus d’informations, consultez index Columnstore décrits.For more information, see Columnstore Indexes Described.

  2. Comprendre les caractéristiques des requêtes les plus fréquemment utilisées.Understand the characteristics of the most frequently used queries. Par exemple, si vous savez qu'une requête fréquemment utilisée crée une jointure entre deux tables ou plus, vous serez plus à même de choisir le type d'index le mieux adapté.For example, knowing that a frequently used query joins two or more tables will help you determine the best type of indexes to use.

  3. Comprendre les caractéristiques des colonnes utilisées dans les requêtes.Understand the characteristics of the columns used in the queries. Par exemple, un index s'avère idéal pour les colonnes associées à des données de type integer et qui sont également uniques ou n'acceptent pas les valeurs NULL.For example, an index is ideal for columns that have an integer data type and are also unique or nonnull columns. Pour les colonnes qui ont des sous-ensembles de données bien définis, utilisez un index filtré dans SQL Server 2008SQL Server 2008 et versions ultérieures.For columns that have well-defined subsets of data, you can use a filtered index in SQL Server 2008SQL Server 2008 and higher versions. Pour plus d'informations, consultez Instructions de conception d'index filtrés dans ce guide.For more information, see Filtered Index Design Guidelines in this guide.

  4. Identifier les options d'index qui peuvent améliorer les performances au moment de la création ou de la maintenance de l'index.Determine which index options might enhance performance when the index is created or maintained. Par exemple, si vous créez un index cluster dans une table volumineuse existante, vous aurez tout intérêt à utiliser l'option d'index ONLINE.For example, creating a clustered index on an existing large table would benefit from the ONLINE index option. Cette option permet en effet la poursuite des activités concurrentes sur les données sous-jacentes pendant la création ou la reconstruction de l'index.The ONLINE option allows for concurrent activity on the underlying data to continue while the index is being created or rebuilt. Pour plus d’informations, consultez Définir les options d’index.For more information, see Set Index Options.

  5. Déterminer l'emplacement de stockage optimal pour l'index.Determine the optimal storage location for the index. Un index non-cluster peut être stocké dans le même groupe de fichiers que celui auquel appartient la table sous-jacente, ou dans un groupe de fichiers distinct.A nonclustered index can be stored in the same filegroup as the underlying table, or on a different filegroup. L'emplacement de stockage des index peut améliorer les performances des requêtes par l'amélioration des performances d'E/S des disques.The storage location of indexes can improve query performance by increasing disk I/O performance. Par exemple, en stockant un index non-cluster dans un groupe de fichiers résidant sur un disque différent de celui du groupe de fichiers de la table, vous pouvez améliorer les performances, car plusieurs disques peuvent être lus simultanément.For example, storing a nonclustered index on a filegroup that is on a different disk than the table filegroup can improve performance because multiple disks can be read at the same time.

    Une autre solution consiste à utiliser un schéma de partition sur plusieurs groupes de fichiers pour les index cluster et non-cluster.Alternatively, clustered and nonclustered indexes can use a partition scheme across multiple filegroups. Le partitionnement permet une gestion plus simple des tables et index volumineux. Vous pouvez en effet accéder à des sous-ensembles de données ou les gérer de manière rapide et efficace, tout en préservant l'intégrité de la collection globale.Partitioning makes large tables or indexes more manageable by letting you access or manage subsets of data quickly and efficiently, while maintaining the integrity of the overall collection. Pour plus d'informations, consultez Partitioned Tables and Indexes.For more information, see Partitioned Tables and Indexes. Si vous envisagez de recourir au partitionnement, vous devez déterminer si l'index doit être aligné, c'est-à-dire, partitionné plus ou moins de la même façon que la table, ou s'il doit être partitionné de façon indépendante.When you consider partitioning, determine whether the index should be aligned, that is, partitioned in essentially the same manner as the table, or partitioned independently.

Consignes générales pour la création d'indexGeneral Index Design Guidelines

Les administrateurs de bases de données expérimentés peuvent concevoir de bons ensembles d'index, mais cette tâche est très complexe, sujette à erreurs et demande beaucoup de temps, même dans le cas de bases de données et de charges de travail peu complexes.Experienced database administrators can design a good set of indexes, but this task is very complex, time-consuming, and error-prone even for moderately complex databases and workloads. La compréhension des caractéristiques de votre base de données, de vos requêtes et de vos colonnes de données peut vous aider à créer des index optimaux.Understanding the characteristics of your database, queries, and data columns can help you design optimal indexes.

Remarques sur la base de donnéesDatabase Considerations

Lorsque vous créez un index, prenez en compte les directives suivantes relatives aux bases de données :When you design an index, consider the following database guidelines:

  • La définition de nombreux index sur une table affecte les performances des instructions INSERT, UPDATE, DELETE et MERGE, car tous les index doivent être mis à jour en conséquence à mesure que les données de la table changent.Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes. Par exemple, si une colonne est utilisée dans plusieurs index et vous exécutez une instruction UPDATE qui modifie les données de cette colonne, chaque index contenant cette colonne doit être mis à jour, ainsi que la colonne de la table de base sous-jacente (segment de mémoire ou index cluster).For example, if a column is used in several indexes and you execute an UPDATE statement that modifies that column's data, each index that contains that column must be updated as well as the column in the underlying base table (heap or clustered index).

    • Évitez que les tables mises à jour ne soient trop abondamment indexées et faites en sorte que les index soient étroits, c'est-à-dire qu'ils comprennent le moins de colonnes possible.Avoid over-indexing heavily updated tables and keep indexes narrow, that is, with as few columns as possible.

    • Utilisez de nombreux index pour améliorer les performances des requêtes sur les tables possédant des besoins réduits en matière de mise à jour, mais de grands volumes de données.Use many indexes to improve query performance on tables with low update requirements, but large volumes of data. Un grand nombre d'index peut améliorer les performances des requêtes qui ne modifient pas les données (instructions SELECT), car l'optimiseur de requête dispose d'un choix d'index plus vaste pour déterminer la méthode d'accès la plus rapide.Large numbers of indexes can help the performance of queries that do not modify data, such as SELECT statements, because the query optimizer has more indexes to choose from to determine the fastest access method.

  • Il n'est peut-être pas idéal d'indexer des tables de taille réduite, car le temps nécessaire à l'optimiseur de requête pour parcourir l'index à la recherche de données peut être supérieur à la durée d'une simple analyse de la table.Indexing small tables may not be optimal because it can take the query optimizer longer to traverse the index searching for data than to perform a simple table scan. Par conséquent, les index de petites tables peuvent ne jamais être utilisés, mais doivent néanmoins être gérés, car les données de la table changent.Therefore, indexes on small tables might never be used, but must still be maintained as data in the table changes.

  • Les index de vues peuvent vous permettre d'améliorer considérablement les performances lorsque la vue contient des agrégations, des jointures de tables ou une combinaison d'agrégations et de jointures.Indexes on views can provide significant performance gains when the view contains aggregations, table joins, or a combination of aggregations and joins. La vue ne doit pas être explicitement référencée dans la requête pour que l'optimiseur de requête puisse l'utiliser.The view does not have to be explicitly referenced in the query for the query optimizer to use it.

  • Servez-vous de l'Assistant Paramétrage du moteur de base de données pour analyser votre base de données et obtenir des recommandations sur les index.Use the Database Engine Tuning Advisor to analyze your database and make index recommendations. Pour plus d'informations, consultez Database Engine Tuning Advisor.For more information, see Database Engine Tuning Advisor.

Remarques sur les requêtesQuery Considerations

Lorsque vous créez un index, prenez en compte les directives suivantes relatives aux requêtes :When you design an index, consider the following query guidelines:

  • Créez des index non cluster sur les colonnes fréquemment utilisées dans des prédicats et des conditions de jointure dans des requêtes.Create nonclustered indexes on the columns that are frequently used in predicates and join conditions in queries. Toutefois, évitez d'ajouter des colonnes superflues.However, you should avoid adding unnecessary columns. L'ajout d'un trop grand nombre de colonnes d'index peut avoir une influence négative sur les performances de gestion des index et de l'espace disque.Adding too many index columns can adversely affect disk space and index maintenance performance.

  • La couverture des index peut améliorer les performances des requêtes, car toutes les données nécessaires pour répondre aux exigences de la requête existent dans l'index proprement dit.Covering indexes can improve query performance because all the data needed to meet the requirements of the query exists within the index itself. Cela signifie que seules les pages d'index, et non les pages de données de la table ou de l'index cluster, sont nécessaires pour récupérer les données demandées, réduisant ainsi globalement le nombre d'E/S des disques.That is, only the index pages, and not the data pages of the table or clustered index, are required to retrieve the requested data; therefore, reducing overall disk I/O. Par exemple, une requête de colonnes a et b sur une table possédant un index composite créé sur les colonnes a, bet c peut récupérer les données spécifiées à partir du seul index.For example, a query of columns a and b on a table that has a composite index created on columns a, b, and c can retrieve the specified data from the index alone.

  • Rédigez des requêtes insérant ou modifiant un maximum de lignes en une seule instruction, plutôt que de recourir à plusieurs requêtes pour mettre à jour les mêmes lignes.Write queries that insert or modify as many rows as possible in a single statement, instead of using multiple queries to update the same rows. De cette façon, la maintenance d'index optimisée peut être exploitée.By using only one statement, optimized index maintenance could be exploited.

  • Évaluez le type de requête et la manière dont les colonnes sont utilisées dans la requête.Evaluate the query type and how columns are used in the query. Par exemple, une colonne utilisée dans un type de requête de correspondance exacte constitue un candidat valable à un index non-cluster ou cluster.For example, a column used in an exact-match query type would be a good candidate for a nonclustered or clustered index.

Remarques sur les colonnesColumn Considerations

Lorsque vous créez un index, prenez en compte les directives suivantes relatives aux colonnes :When you design an index consider the following column guidelines:

  • Veillez à ce que la clé d'index des index cluster soit courte.Keep the length of the index key short for clustered indexes. En outre, les index cluster bénéficient du fait d'être créés sur des colonnes uniques ou non NULL.Additionally, clustered indexes benefit from being created on unique or nonnull columns.

  • Les colonnes de types de données ntext, text, image, varchar(max), nvarchar(max) et varbinary(max) ne peuvent pas être spécifiées comme colonnes de clés d'index.Columns that are of the ntext, text, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot be specified as index key columns. Cependant, les types de données varchar(max), nvarchar(max), varbinary(max) et xml peuvent participer à des index non-cluster en tant que colonnes d'index non clé.However, varchar(max), nvarchar(max), varbinary(max), and xml data types can participate in a nonclustered index as nonkey index columns. Pour plus d'informations, consultez la section Index avec colonnes inclusesdans ce guide.For more information, see the section 'Index with Included Columns' in this guide.

  • Un type de données xml ne peut être qu'une colonne clé dans un index XML.An xml data type can only be a key column only in an XML index. Pour plus d’informations, consultez Index XML (SQL Server).For more information, see XML Indexes (SQL Server). SQL Server 2012 SP1 introduit un nouveau type d'index XML appelé index XML sélectif.SQL Server 2012 SP1 introduces a new type of XML index known as a Selective XML Index. Ce nouvel index améliore les performances de requête sur les données stockées en XML dans SQL Server, permettant ainsi d'indexer plus rapidement les charges de travail comportant beaucoup de données XML et améliorant l'évolutivité en réduisant les coûts de stockage de l'index en lui-même.This new index can improve querying performance over data stored as XML in SQL Server, allow for much faster indexing of large XML data workloads, and improve scalability by reducing storage costs of the index itself. Pour plus d’informations, consultez Index XML sélectifs (SXI).For more information, see Selective XML Indexes (SXI).

  • Vérifiez l'unicité des colonnes.Examine column uniqueness. Un index unique plutôt que non unique sur la même combinaison de colonnes procure des informations supplémentaires à l'optimiseur de requête, ce qui améliore l'utilité de l'index.A unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that makes the index more useful. Pour plus d'informations, consultez Instructions de conception d'index uniques dans ce guide.For more information, see Unique Index Design Guidelines in this guide.

  • Examinez la distribution des données dans la colonne.Examine data distribution in the column. Bien souvent, la longueur d'exécution d'une requête est due à l'indexation d'une colonne comportant peu de valeurs uniques ou à la réalisation d'une jointure sur ce type de colonne.Frequently, a long-running query is caused by indexing a column with few unique values, or by performing a join on such a column. Il s'agit d'un problème crucial pour les données et la requête, que l'on ne peut généralement pas résoudre sans identifier clairement la situation.This is a fundamental problem with the data and query, and generally cannot be resolved without identifying this situation. Un répertoire téléphonique physique, par exemple, trié dans l'ordre alphabétique par nom de famille, ne permettra pas l'identification rapide d'une personne si tous les habitants de la ville se nomment Smith ou Jones.For example, a physical telephone directory sorted alphabetically on last name will not expedite locating a person if all people in the city are named Smith or Jones. Pour plus d'informations sur la distribution de données, consultez Statistics.For more information about data distribution, see Statistics.

  • Envisagez d'utiliser des index filtrés sur les colonnes qui ont des sous-ensembles bien définis, par exemple les colonnes éparses, les colonnes contenant principalement des valeurs NULL, les colonnes contenant des catégories de valeurs et les colonnes contenant des plages de valeurs distinctes.Consider using filtered indexes on columns that have well-defined subsets, for example sparse columns, columns with mostly NULL values, columns with categories of values, and columns with distinct ranges of values. Un index filtré bien conçu peut améliorer les performances des requêtes et réduire les coûts de maintenance et de stockage.A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce storage costs.

  • Tenez compte de l'ordre des colonnes si l'index doit en contenir plusieurs.Consider the order of the columns if the index will contain multiple columns. La colonne utilisée dans la clause WHERE au sein d'une condition de recherche de type égal à (=), supérieur à (>), inférieur à (<) ou BETWEEN, ou qui participe à une jointure, doit être insérée en premier.The column that is used in the WHERE clause in an equal to (=), greater than (>), less than (<), or BETWEEN search condition, or participates in a join, should be placed first. Les colonnes supplémentaires doivent être classées en fonction de leur niveau de différenciation, c'est-à-dire de la plus distincte à la moins distincte.Additional columns should be ordered based on their level of distinctness, that is, from the most distinct to the least distinct.

    Par exemple, si l'index est défini en tant que LastName, la valeur FirstName de l'index sera utile si la condition de recherche est WHERE LastName = 'Smith' ou WHERE LastName = Smith AND FirstName LIKE 'J%'.For example, if the index is defined as LastName, FirstName the index will be useful when the search criterion is WHERE LastName = 'Smith' or WHERE LastName = Smith AND FirstName LIKE 'J%'. Cependant, l'optimiseur de requête n'utilise pas l'index pour une requête portant uniquement sur FirstName (WHERE FirstName = 'Jane').However, the query optimizer would not use the index for a query that searched only on FirstName (WHERE FirstName = 'Jane').

  • Pensez à indexer les colonnes calculées.Consider indexing computed columns. Pour plus d'informations, consultez Indexes on Computed Columns.For more information, see Indexes on Computed Columns.

Caractéristiques des indexIndex Characteristics

Après avoir déterminé qu'un index est approprié pour une requête, vous pouvez sélectionner le type d'index qui convient le mieux à la situation.After you have determined that an index is appropriate for a query, you can select the type of index that best fits your situation. Un index doit posséder les caractéristiques suivantes :Index characteristics include the following:

  • être cluster ou non-cluster ;Clustered versus nonclustered

  • être unique ou non unique ;Unique versus nonunique

  • être à une ou plusieurs colonnes ;Single column versus multicolumn

  • être trié par ordre croissant ou décroissant d'après les colonnes qui le constituent ;Ascending or descending order on the columns in the index

  • table entière plutôt que filtré pour les index non cluster.Full-table versus filtered for nonclustered indexes

Vous pouvez également personnaliser les caractéristiques de stockage initiales de l'index afin d'optimiser ses performances ou sa maintenance en définissant une option telle que FILLFACTOR.You can also customize the initial storage characteristics of the index to optimize its performance or maintenance by setting an option such as FILLFACTOR. Vous pouvez également déterminer l'emplacement de stockage de l'index en utilisant des groupes de fichiers ou des schémas de partition pour optimiser les performances.Also, you can determine the index storage location by using filegroups or partition schemes to optimize performance.

Placement d'index sur les groupes de fichiers ou les schémas de partitionIndex Placement on Filegroups or Partitions Schemes

Lors du développement de votre stratégie de conception des index, vous devez tenir compte du placement de ces index sur les groupes de fichiers associés à la base de données.As you develop your index design strategy, you should consider the placement of the indexes on the filegroups associated with the database. Une sélection rigoureuse du groupe de fichiers ou du schéma de partition peut améliorer les performances des requêtes.Careful selection of the filegroup or partition scheme can improve query performance.

Par défaut, les index sont stockés dans le même groupe de fichiers que la table de base sur laquelle est créé l'index.By default, indexes are stored in the same filegroup as the base table on which the index is created. Un index cluster non partitionné et la table de base résident toujours dans le même groupe de fichiers.A nonpartitioned clustered index and the base table always reside in the same filegroup. Toutefois, vous pouvez effectuer les opérations suivantes :However, you can do the following:

  • créer des index non cluster dans un groupe de fichiers différent de celui de la table de base ou de l'index cluster ;Create nonclustered indexes on a filegroup other than the filegroup of the base table or clustered index.

  • partitionner des index cluster et non-cluster pour qu'ils concernent plusieurs groupes de fichiers ;Partition clustered and nonclustered indexes to span multiple filegroups.

  • déplacer une table d'un groupe de fichiers à un autre en supprimant l'index cluster et en spécifiant un nouveau groupe de fichiers ou un nouveau schéma de partition dans la clause MOVE TO de l'instruction DROP INDEX ou en utilisant l'instruction CREATE INDEX avec la clause DROP_EXISTING.Move a table from one filegroup to another by dropping the clustered index and specifying a new filegroup or partition scheme in the MOVE TO clause of the DROP INDEX statement or by using the CREATE INDEX statement with the DROP_EXISTING clause.

Créer l'index non-cluster dans un autre groupe de fichiers permet de réaliser des gains de performances si les groupes de fichiers utilisent des lecteurs physiques différents avec leurs propres contrôleurs.By creating the nonclustered index on a different filegroup, you can achieve performance gains if the filegroups are using different physical drives with their own controllers. Les informations d'index et les données peuvent alors être lues en parallèle par plusieurs têtes de disques.Data and index information can then be read in parallel by the multiple disk heads. Par exemple, si la Table_A du groupe de fichiers f1 et l' Index_A du groupe de fichiers f2 sont utilisés par la même requête, des gains de performances sont possibles, car les deux groupes de fichiers sont utilisés totalement sans contention.For example, if Table_A on filegroup f1 and Index_A on filegroup f2 are both being used by the same query, performance gains can be achieved because both filegroups are being fully used without contention. Mais si la Table_A est analysée par la requête et si l' Index_A n'est pas référencé, seul le groupe de fichiers f1 est utilisé,However, if Table_A is scanned by the query but Index_A is not referenced, only filegroup f1 is used. ce qui n'apporte aucun gain de performance.This creates no performance gain.

Comme vous ne pouvez pas prévoir le type d'accès qui se met en place ni le moment de cette mise en place, il peut s'avérer plus judicieux de répartir vos tables et vos index sur tous les groupes de fichiers.Because you cannot predict what type of access will occur and when it will occur, it could be a better decision to spread your tables and indexes across all filegroups. Ceci garantit l'accès à tous les disques, car toutes les données et tous les index sont répartis uniformément sur tous les disques, quel que soit le mode d'accès aux données.This would guarantee that all disks are being accessed because all data and indexes are spread evenly across all disks, regardless of which way the data is accessed. Cette approche est également plus simple pour les administrateurs système.This is also a simpler approach for system administrators.

Partitions sur plusieurs groupes de fichiersPartitions Across Multiple Filegroups

Vous pouvez également envisager de partitionner des index cluster et non-cluster sur plusieurs groupes de fichiers.You can also consider partitioning clustered and nonclustered indexes across multiple filegroups. Les index partitionnés sont partitionnés horizontalement ou par ligne, selon la fonction de partition.Partitioned indexes are partitioned horizontally, or by row, based on a partition function. La fonction de partition définit le mode de mappage de chaque ligne sur un ensemble de partitions basé sur les valeurs de certaines colonnes, nommées colonnes de partition.The partition function defines how each row is mapped to a set of partitions based on the values of certain columns, called partitioning columns. Un schéma de partition spécifie le mappage des partitions sur un ensemble de groupe de fichiers.A partition scheme specifies the mapping of the partitions to a set of filegroups.

Le partitionnement d'un index peut présenter les avantages suivants :Partitioning an index can provide the following benefits:

  • Systèmes évolutifs permettant de gérer plus facilement les grands index.Provide scalable systems that make large indexes more manageable. Par exemple, les systèmes OLTP peuvent mettre en œuvre des applications sensibles aux partitions qui se chargent des grands index.OLTP systems, for example, can implement partition-aware applications that deal with large indexes.

  • Exécution plus rapide et plus efficace des requêtes.Make queries run faster and more efficiently. Lorsque des requêtes accèdent à plusieurs partitions d'un index, l'optimiseur de requête peut traiter plusieurs partitions individuelles en même temps et exclure les partitions qui ne sont pas concernées par la requête.When queries access several partitions of an index, the query optimizer can process individual partitions at the same time and exclude partitions that are not affected by the query.

Pour plus d’informations, consultez Tables et index partitionnés.For more information, see Partitioned Tables and Indexes.

Indications pour la conception de l'ordre de tri des indexIndex Sort Order Design Guidelines

Lorsque vous définissez des index, vous devez déterminer si les données de la colonne clé d'index doivent être stockées dans l'ordre croissant ou décroissant.When defining indexes, you should consider whether the data for the index key column should be stored in ascending or descending order. L'ordre croissant est l'option par défaut et maintient la compatibilité avec les versions antérieures de SQL ServerSQL Server.Ascending is the default and maintains compatibility with earlier versions of SQL ServerSQL Server. La syntaxe des instructions CREATE INDEX, CREATE TABLE et ALTER TABLE permet l'application des mot clés ASC (croissant) et DESC (décroissant) à chaque colonne d'un index et d'une contrainte.The syntax of the CREATE INDEX, CREATE TABLE, and ALTER TABLE statements supports the keywords ASC (ascending) and DESC (descending) on individual columns in indexes and constraints.

La spécification de l'ordre dans lequel les valeurs de clé sont stockées dans un index est utile lorsque les requêtes référençant la table possèdent des clauses ORDER BY qui définissent différents sens pour la ou les colonnes clés de cet index.Specifying the order in which key values are stored in an index is useful when queries referencing the table have ORDER BY clauses that specify different directions for the key column or columns in that index. Dans ces situations, l'index peut supprimer la nécessité d'un opérateur SORT dans le plan de requête, ce qui rend la requête plus efficace.In these cases, the index can remove the need for a SORT operator in the query plan; therefore, this makes the query more efficient. Par exemple, les acheteurs du service achat de Adventure Works CyclesAdventure Works Cycles doivent évaluer la qualité des produits qu'ils acquièrent auprès des fournisseurs.For example, the buyers in the Adventure Works CyclesAdventure Works Cycles purchasing department have to evaluate the quality of products they purchase from vendors. Les acheteurs souhaitent notamment rechercher, parmi les produits envoyés par ces fournisseurs, ceux qui affichent un degré de rejet élevé.The buyers are most interested in finding products sent by these vendors with a high rejection rate. Comme le montre la requête suivante, l’extraction des données en fonction de ce critère nécessite que la colonne RejectedQty de la table Purchasing.PurchaseOrderDetail soit triée dans l’ordre décroissant (de la valeur la plus élevée à la valeur la plus faible), et que la colonne ProductID soit triée dans l’ordre croissant (de la valeur la plus faible à la valeur la plus élevée).As shown in the following query, retrieving the data to meet this criteria requires the RejectedQty column in the Purchasing.PurchaseOrderDetail table to be sorted in descending order (large to small) and the ProductID column to be sorted in ascending order (small to large).

SELECT RejectedQty, ((RejectedQty/OrderQty)*100) AS RejectionRate,  
    ProductID, DueDate  
FROM Purchasing.PurchaseOrderDetail  
ORDER BY RejectedQty DESC, ProductID ASC;  

Le plan d'exécution ci-dessous pour cette requête montre que l'optimiseur de requête a utilisé un opérateur SORT pour retourner l'ensemble de résultats dans l'ordre spécifié par la clause ORDER BY.The following execution plan for this query shows that the query optimizer used a SORT operator to return the result set in the order specified by the ORDER BY clause.

Plan d’exécution indique un opérateur SORT est utilisé. Execution plan shows a SORT operator is used.

Si un index est créé avec les colonnes clés correspondant à celles de la clause ORDER BY de la requête, l'opérateur SORT peut être supprimé du plan de requête, ce qui rend celui-ci plus efficace.If an index is created with key columns that match those in the ORDER BY clause in the query, the SORT operator can be eliminated in the query plan and the query plan is more efficient.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty  
ON Purchasing.PurchaseOrderDetail  
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);  

Une fois la requête réexécutée, le plan d'exécution ci-dessous montre que l'opérateur SORT a été supprimé et que l'index non-cluster nouvellement créé est utilisé.After the query is executed again, the following execution plan shows that the SORT operator has been eliminated and the newly created nonclustered index is used.

Plan d’exécution indique un tri opérateur n’est pas utiliséExecution plan shows a SORT operator is not used

Le Moteur de base de donnéesDatabase Engine peut parcourir les données aussi efficacement dans un sens que dans l'autre.The Moteur de base de donnéesDatabase Engine can move equally efficiently in either direction. Un index défini sous la forme (RejectedQty DESC, ProductID ASC) peut néanmoins être utilisé pour une requête dont la clause ORDER BY inverse le sens du tri des colonnes.An index defined as (RejectedQty DESC, ProductID ASC) can still be used for a query in which the sort direction of the columns in the ORDER BY clause are reversed. Par exemple, une requête possédant la clause ORDER BY ORDER BY RejectedQty ASC, ProductID DESC peut utiliser l'index.For example, a query with the ORDER BY clause ORDER BY RejectedQty ASC, ProductID DESC can use the index.

L'ordre de tri ne peut être spécifié que pour les colonnes clés.Sort order can be specified only for key columns. L’affichage catalogue sys.index_columns et la fonction INDEXKEY_PROPERTY indiquent si une colonne d’index est stockée dans l’ordre croissant ou décroissant.The sys.index_columns catalog view and the INDEXKEY_PROPERTY function report whether an index column is stored in ascending or descending order.

Icône de flèche utilisée avec le lien Retour au début dans ce GuideArrow icon used with Back to Top link In This Guide

Indications pour la conception d'index clusterClustered Index Design Guidelines

Les index cluster trient et stockent les lignes de données de la table en fonction de leurs valeurs de clé.Clustered indexes sort and store the data rows in the table based on their key values. Il n'y a qu'un index cluster par table car les lignes de données ne peuvent être triées que dans un seul ordre.There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. À quelques exceptions près, toutes les tables doivent avoir un index cluster défini sur la ou les colonnes présentant les caractéristiques suivantes :With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following:

  • utilisables pour les requêtes fréquemment utilisées ;Can be used for frequently used queries.

  • assurant un niveau élevé d'unicité ;Provide a high degree of uniqueness.

    Notes

    Lorsque vous créez une contrainte PRIMARY KEY, un index unique sur la ou les colonnes est automatiquement créé.When you create a PRIMARY KEY constraint, a unique index on the column, or columns, is automatically created. Par défaut, cet index est cluster ; toutefois, vous pouvez spécifier un index non-cluster lorsque vous créez la contrainte.By default, this index is clustered; however, you can specify a nonclustered index when you create the constraint.

  • utilisables dans les requêtes de plage.Can be used in range queries.

Si l'index cluster n'est pas créé avec la propriété UNIQUE, le Moteur de base de donnéesDatabase Engine ajoute automatiquement une colonne d'indicateur d'unicité de 4 octets à la table.If the clustered index is not created with the UNIQUE property, the Moteur de base de donnéesDatabase Engine automatically adds a 4-byte uniqueifier column to the table. Si nécessaire, le Moteur de base de donnéesDatabase Engine ajoute automatiquement une valeur d'indicateur d'unicité une ligne pour que chaque clé soit unique.When it is required, the Moteur de base de donnéesDatabase Engine automatically adds a uniqueifier value to a row to make each key unique. Cette colonne et ses valeurs sont utilisées en interne et ne sont ni affichables, ni accessibles par les utilisateurs.This column and its values are used internally and cannot be seen or accessed by users.

Architecture des index clusterClustered Index Architecture

Dans SQL ServerSQL Server, les index sont organisés en arborescences binaires.In SQL ServerSQL Server, indexes are organized as B-trees. Chaque page d'une arborescence binaire d'index s'appelle un nœud d'index.Each page in an index B-tree is called an index node. Le nœud supérieur d'une arborescence binaire est le nœud racine.The top node of the B-tree is called the root node. Les nœuds du niveau inférieur de l'index sont appelés les nœuds feuille.The bottom nodes in the index are called the leaf nodes. Tous les niveaux d'index situés entre la racine et les nœuds feuille s'appellent des niveaux intermédiaires.Any index levels between the root and the leaf nodes are collectively known as intermediate levels. Dans un index cluster, les nœuds feuille contiennent les pages de données de la table sous-jacente.In a clustered index, the leaf nodes contain the data pages of the underlying table. Les nœuds racine et de niveau intermédiaire contiennent les pages d'index dans lesquelles se trouvent les lignes d'index.The root and intermediate level nodes contain index pages holding index rows. Chaque ligne d'index contient une valeur de clé et un pointeur vers une page de niveau intermédiaire dans l'arborescence binaire ou vers une ligne de données dans le niveau feuille de l'index.Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. Les pages de chaque niveau de l'index sont liées dans une liste à double liaison.The pages in each level of the index are linked in a doubly-linked list.

Un index cluster a une ligne dans sys.partitions, où index_id = 1 pour chaque partition utilisée par celui-ci.Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index. Par défaut, un index cluster possède une seule partition.By default, a clustered index has a single partition. Lorsqu'un index cluster détient plusieurs partitions, chacune d'elles possède une structure d'arborescence binaire qui contient ses données.When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. Par exemple, si un index cluster possède quatre partitions, il existe quatre structures d'arborescence binaire, à raison d'une dans chaque partition.For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition.

Suivant les types de données de l'index cluster, chaque structure d'index cluster possède une ou plusieurs unités d'allocation pour le stockage et la gestion des données d'une partition spécifique.Depending on the data types in the clustered index, each clustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. Au minimum, chaque index cluster détient une unité d'allocation IN_ROW_DATA par partition.At a minimum, each clustered index will have one IN_ROW_DATA allocation unit per partition. L'index cluster possède également une unité d'allocation LOB_DATA par partition s'il contient des colonnes LOB (Large Object).The clustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns. En outre, il détient une unité d'allocation ROW_OVERFLOW_DATA par partition s'il contient des colonnes de longueur variable qui dépassent la limite de taille de ligne de 8 060 octets.It will also have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit.

Les pages de la chaîne de données et les lignes qu'elles rassemblent sont organisées en fonction de la valeur de la clé d'index cluster.The pages in the data chain and the rows in them are ordered on the value of the clustered index key. Toutes les insertions sont faites à l'endroit où la valeur de clé de la ligne insérée correspond parfaitement à la séquence de tri parmi les lignes existantes.All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows.

L'illustration suivante montre la structure d'un index cluster dans une partition unique.This illustration shows the structure of a clustered index in a single partition.

Niveaux d’un index clusterLevels of a clustered index

Remarques sur les requêtesQuery Considerations

Avant de créer des index cluster, il est important de comprendre le mode d'accès aux données.Before you create clustered indexes, understand how your data will be accessed. Envisagez l'emploi d'un index cluster pour les requêtes qui :Consider using a clustered index for queries that do the following:

  • retournent une plage de valeurs en utilisant des opérateurs tels que BETWEEN, >, >=, < et <=.Return a range of values by using operators such as BETWEEN, >, >=, <, and <=.

    Dès que la ligne comportant la première valeur est trouvée à l'aide de l'index cluster, les lignes présentant les valeurs indexées suivantes sont garanties comme étant adjacentes physiquement.After the row with the first value is found by using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. Par exemple, si une requête extrait des enregistrements compris dans une plage de numéros de commandes, un index cluster sur la colonne SalesOrderNumber permet de localiser rapidement la ligne qui contient le premier numéro de commande, puis d'extraire toutes les lignes successives de la table jusqu'à ce que le dernier numéro de commande soit atteint.For example, if a query retrieves records between a range of sales order numbers, a clustered index on the column SalesOrderNumber can quickly locate the row that contains the starting sales order number, and then retrieve all successive rows in the table until the last sales order number is reached.

  • retournent des jeux de résultats volumineux ;Return large result sets.

  • utilisent des clauses JOIN ; ce sont en général des colonnes clés étrangères ;Use JOIN clauses; typically these are foreign key columns.

  • utilisent des clauses ORDER BY ou GROUP BY.Use ORDER BY, or GROUP BY clauses.

    Si un index est présent sur les colonnes spécifiées dans la clause ORDER BY ou GROUP BY, le Moteur de base de donnéesDatabase Engine n'a plus besoin de trier les données car les lignes le sont déjà.An index on the columns specified in the ORDER BY or GROUP BY clause may remove the need for the Moteur de base de donnéesDatabase Engine to sort the data, because the rows are already sorted. Les requêtes présentent dès lors des performances accrues.This improves query performance.

Remarques sur les colonnesColumn Considerations

En général, vous devez définir la clé d'index cluster avec le moins de colonnes possible.Generally, you should define the clustered index key with as few columns as possible. Envisagez les colonnes présentant un ou plusieurs des attributs suivants :Consider columns that have one or more of the following attributes:

  • Colonnes uniques ou qui contiennent de nombreuses valeurs distinctesAre unique or contain many distinct values

    Par exemple, l'ID d'un salarié l'identifie de manière unique.For example, an employee ID uniquely identifies employees. Un index cluster ou une contrainte PRIMARY KEY sur la colonne EmployeeID améliore les performances des requêtes qui recherchent des informations sur les salariés en fonction de leur ID.A clustered index or PRIMARY KEY constraint on the EmployeeID column would improve the performance of queries that search for employee information based on the employee ID number. D'une autre manière, un index cluster peut être créé sur LastName, FirstName, MiddleName , car les enregistrements de salariés sont fréquemment groupés et interrogés de cette façon et l'association de ces colonnes présente toujours un niveau élevé de différenciation.Alternatively, a clustered index could be created on LastName, FirstName, MiddleName because employee records are frequently grouped and queried in this way, and the combination of these columns would still provide a high degree of difference.

  • Accès séquentiel des colonnesAre accessed sequentially

    Par exemple, l'ID d'un produit l'identifie de manière unique dans la table Production.Product de la base de données AdventureWorks2012AdventureWorks2012 .For example, a product ID uniquely identifies products in the Production.Product table in the AdventureWorks2012AdventureWorks2012 database. Les requêtes dans lesquelles une recherche séquentielle est spécifiée, telles que WHERE ProductID BETWEEN 980 and 999, tireront parti d'un index cluster sur ProductID.Queries in which a sequential search is specified, such as WHERE ProductID BETWEEN 980 and 999, would benefit from a clustered index on ProductID. car les lignes sont stockées dans l'ordre sur cette colonne clé.This is because the rows would be stored in sorted order on that key column.

  • Défini comme IDENTITY.Defined as IDENTITY.

  • Colonnes fréquemment utilisées pour trier les données extraites d'une tableUsed frequently to sort the data retrieved from a table.

    Il peut être judicieux de mettre en cluster, c'est-à-dire de trier physiquement, la table sur cette colonne pour économiser le coup d'une opération de tri à chaque fois que la colonne est interrogée.It can be a good idea to cluster, that is physically sort, the table on that column to save the cost of a sort operation every time the column is queried.

Les index cluster sont déconseillés pour les colonnes présentant les attributs suivants :Clustered indexes are not a good choice for the following attributes:

  • Les colonnes sujettes à des modifications fréquentes.Columns that undergo frequent changes

    La ligne tout entière est ainsi déplacée, car le Moteur de base de donnéesDatabase Engine doit conserver les valeurs des données de la ligne dans l'ordre physique.This causes in the whole row to move, because the Moteur de base de donnéesDatabase Engine must keep the data values of a row in physical order. Cette observation est importante dans les systèmes de traitement transactionnel à haut volume où les données sont en général éphémères.This is an important consideration in high-volume transaction processing systems in which data is typically volatile.

  • Les clés étendues.Wide keys

    Les clés étendues sont composées de plusieurs colonnes ou plusieurs colonnes de grande taille.Wide keys are a composite of several columns or several large-size columns. Les valeurs de clé de l'index cluster sont utilisées par tous les index non-cluster comme clés de recherche.The key values from the clustered index are used by all nonclustered indexes as lookup keys. Tous les index non-cluster définis sur la même table sont considérablement plus grands car leurs entrées contiennent la clé de cluster et aussi les colonnes clés définies pour cet index non-cluster.Any nonclustered indexes defined on the same table will be significantly larger because the nonclustered index entries contain the clustering key and also the key columns defined for that nonclustered index.

Icône de flèche utilisée avec le lien Retour au début dans ce GuideArrow icon used with Back to Top link In This Guide

Indications pour la conception d'index non-clusterNonclustered Index Design Guidelines

Un index non-cluster contient les valeurs de clé d'index et les localisateurs de ligne qui pointent vers l'emplacement de stockage des données de table.A nonclustered index contains the index key values and row locators that point to the storage location of the table data. Vous pouvez créer plusieurs index non cluster sur une table ou une vue indexée.You can create multiple nonclustered indexes on a table or indexed view. Les index non-cluster doivent, en principe, améliorer les performances des requêtes fréquemment utilisées qui ne sont pas couvertes par l'index cluster.Generally, nonclustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index.

De la même manière que vous utilisez un index dans un livre, l'optimiseur de requête recherche une valeur de données en examinant l'index non-cluster afin de trouver l'emplacement qu'occupe la valeur dans la table, puis récupère directement les données à partir de cet emplacement.Similar to the way you use an index in a book, the query optimizer searches for a data value by searching the nonclustered index to find the location of the data value in the table and then retrieves the data directly from that location. C'est pour cette raison que les index non cluster constituent une solution idéale pour les requêtes à correspondance exacte ; l'index contient en effet des entrées décrivant l'emplacement exact qu'occupent dans la table les valeurs de données recherchées dans les requêtes.This makes nonclustered indexes the optimal choice for exact match queries because the index contains entries describing the exact location in the table of the data values being searched for in the queries. Par exemple, pour interroger la table HumanResources. Employee pour tous les employés qui réfèrent à un responsable spécifique, l'optimiseur de requête peut utiliser l'index non cluster IX_Employee_ManagerID; sa colonne clé est ManagerID .For example, to query the HumanResources. Employee table for all employees that report to a specific manager, the query optimizer might use the nonclustered index IX_Employee_ManagerID; this has ManagerID as its key column. L'optimiseur de requête recherche rapidement toutes les entrées de l'index qui correspondent à la valeur ManagerIDspécifiée.The query optimizer can quickly find all entries in the index that match the specified ManagerID. Chaque entrée d'index pointe vers la page et la ligne exactes de la table ou de l'index cluster contenant les données correspondantes.Each index entry points to the exact page and row in the table, or clustered index, in which the corresponding data can be found. Après avoir trouvé toutes les entrées dans l'index, l'optimiseur de requête peut accéder directement à la page et à la ligne exactes pour récupérer les données.After the query optimizer finds all entries in the index, it can go directly to the exact page and row to retrieve the data.

Architecture des index non clusterNonclustered Index Architecture

Les index non-cluster possèdent la même structure arborescente binaire que les index cluster, à ces différences près :Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:

  • Les lignes de données de la table sous-jacente ne sont pas triées et stockées dans l'ordre des clés non cluster.The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.

  • La couche inférieure d'un index non-cluster n'est pas constituée de pages de données, mais de pages d'index.The leaf layer of a nonclustered index is made up of index pages instead of data pages.

Dans les lignes des index non-cluster, le localisateur est soit un pointeur vers une ligne, soit une clé d'index cluster :The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

  • Si la table est un segment de mémoire (dépourvue d'index cluster), le localisateur de ligne est un pointeur vers la ligne.If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. Le pointeur est construit à partir de l'ID du fichier, du numéro de la page et du numéro de ligne dans la page.The pointer is built from the file identifier (ID), page number, and number of the row on the page. Le pointeur complet est appelé une ID de ligne (RID).The whole pointer is known as a Row ID (RID).

  • Si la table a un index cluster, ou si l'index est sur une vue indexée, le localisateur de ligne est la clé d'index cluster pour la ligne.If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row.

Les index non-cluster comprennent une ligne dans sys.partitionsindex_id >1 pour chaque partition utilisée par l’index.Nonclustered indexes have one row in sys.partitions with index_id >1 for each partition used by the index. Par défaut, un index non-cluster contient une seule partition.By default, a nonclustered index has a single partition. Lorsqu'un index non-cluster comprend plusieurs partitions, chaque partition a une structure arborescente binaire qui contient les lignes d'index correspondantes.When a nonclustered index has multiple partitions, each partition has a B-tree structure that contains the index rows for that specific partition. Par exemple, si un index non-cluster a quatre partitions, il y a quatre arborescences binaires, une dans chaque partition.For example, if a nonclustered index has four partitions, there are four B-tree structures, with one in each partition.

En fonction des types de données de l'index non-cluster, chaque structure d'index non-cluster aura une ou plusieurs unités d'allocation dans lesquelles stocker et gérer les données d'une partition spécifique.Depending on the data types in the nonclustered index, each nonclustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. Chaque index non-cluster aura au minimum une unité d'allocation IN_ROW_DATA par partition pour stocker les pages de l'arborescence binaire de l'index.At a minimum, each nonclustered index will have one IN_ROW_DATA allocation unit per partition that stores the index B-tree pages. L'index non-cluster aura également une unité d'allocation LOB_DATA par partition s'il contient des colonnes d'objets volumineux (LOB).The nonclustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns . Il aura par ailleurs une unité d'allocation ROW_OVERFLOW_DATA par partition s'il contient des colonnes de longueur variable dont les lignes dépassent 8 060 octets.Additionally, it will have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit.

L'illustration suivante montre la structure d'un index non-cluster avec une seule partition.The following illustration shows the structure of a nonclustered index in a single partition.

Niveaux d’un index non-clusterLevels of a nonclustered index

Remarques sur la base de donnéesDatabase Considerations

Les caractéristiques de la base de données sont importantes lors de la conception d'index non-cluster.Consider the characteristics of the database when designing nonclustered indexes.

  • Les bases de données ou les tables dont les mises à jour sont faibles, mais qui contiennent des volumes importants de données peuvent tirer parti de nombreux index non-cluster en vue d'améliorer les performances des requêtes.Databases or tables with low update requirements, but large volumes of data can benefit from many nonclustered indexes to improve query performance. Envisagez de créer des index filtrés pour les sous-ensembles de données bien définis afin d'améliorer les performances des requêtes, réduire les coûts de stockage d'index et réduire les coûts de maintenance d'index par rapport aux index non cluster de table entière.Consider creating filtered indexes for well-defined subsets of data to improve query performance, reduce index storage costs, and reduce index maintenance costs compared with full-table nonclustered indexes.

    Les applications et bases de données d'aide à la décision contenant principalement des données en lecture seule peuvent tirer parti de nombreux index non-cluster.Decision Support System applications and databases that contain primarily read-only data can benefit from many nonclustered indexes. L'optimiseur de requête doit choisir parmi davantage d'index pour déterminer la méthode d'accès la plus rapide ; les caractéristiques de mise à jour faible de la base de données sont synonymes d'une maintenance d'index qui n'entravera pas les performances.The query optimizer has more indexes to choose from to determine the fastest access method, and the low update characteristics of the database mean index maintenance will not impede performance.

  • Les applications et bases de données OLTP (traitement transactionnel en ligne) qui contiennent des tables largement mises à jour doivent éviter la sur-indexation.Online Transaction Processing applications and databases that contain heavily updated tables should avoid over-indexing. Les index doivent en outre être réduits, c'est-à-dire contenir le moins de colonnes possible.Additionally, indexes should be narrow, that is, with as few columns as possible.

    La définition de nombreux index sur une table affecte les performances des instructions INSERT, UPDATE, DELETE et MERGE , car à mesure que les données de la table changent, tous les index doivent être mis à jour en conséquence.Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes.

Remarques sur les requêtesQuery Considerations

Avant de créer des index non-cluster, vous devez comprendre comment se déroulera l'accès aux données.Before you create nonclustered indexes, you should understand how your data will be accessed. Il est conseillé d'utiliser un index non-cluster pour les requêtes avec les attributs suivants :Consider using a nonclustered index for queries that have the following attributes:

  • Requêtes qui utilisent des clauses JOIN ou GROUP BY.Use JOIN or GROUP BY clauses.

    Créez plusieurs index non-cluster sur des colonnes impliquées dans les opérations de jointure et de regroupement, ainsi qu'un index cluster sur les colonnes clés étrangère éventuelles.Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.

  • Requêtes qui ne retournent pas des ensembles de résultats volumineux.Queries that do not return large result sets.

    Créez des index filtrés pour couvrir les requêtes qui retournent un sous-ensemble bien défini de lignes d'une grande table.Create filtered indexes to cover queries that return a well-defined subset of rows from a large table.

  • Requêtes qui contiennent des colonnes souvent impliquées dans les conditions de recherche d'une requête (clause WHERE) qui retournent des correspondances exactes.Contain columns frequently involved in search conditions of a query, such as WHERE clause, that return exact matches.

Remarques sur les colonnesColumn Considerations

Il est conseillé d'utiliser des colonnes qui possèdent un ou plusieurs de ces attributs :Consider columns that have one or more of these attributes:

  • Couvrent la requête.Cover the query.

    Performances accrues lorsque l'index contient toutes les colonnes de la requête.Performance gains are achieved when the index contains all columns in the query. L'optimiseur de requête peut localiser toutes les valeurs de colonnes dans l'index ; les données de table ou d'index cluster ne sont pas accédées, avec pour conséquence une réduction des opérations d'E/S disque.The query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations. Utilisez un index avec colonnes incluses pour ajouter des colonnes de couverture au lieu de créer une clé d'index de grande taille.Use index with included columns to add covering columns instead of creating a wide index key.

    Si la table a un index cluster, la ou les colonnes définies dans cet index sont automatiquement ajoutées à la fin de chaque index non-cluster de la table.If the table has a clustered index, the column or columns defined in the clustered index are automatically appended to the end of each nonclustered index on the table. Ceci peut produire une requête couverte sans spécifier les colonnes de l'index cluster dans la définition de l'index non-cluster.This can produce a covered query without specifying the clustered index columns in the definition of the nonclustered index. Par exemple, si une table a un index cluster sur la colonne C, un index non cluster sur les colonnes B et A aura comme valeurs de clé les colonnes B, Aet C.For example, if a table has a clustered index on column C, a nonclustered index on columns B and A will have as its key values columns B, A, and C.

  • Un nombre élevé de valeurs distinctes, comme une combinaison de nom et prénom, si un index cluster est utilisé pour d'autres colonnes.Lots of distinct values, such as a combination of last name and first name, if a clustered index is used for other columns.

    Lorsqu'il existe très peu de valeurs distinctes (1 et 0 uniquement, par exemple), la plupart des requêtes utiliseront une analyse de table, généralement plus efficace, au lieu de l'index.If there are very few distinct values, such as only 1 and 0, most queries will not use the index because a table scan is generally more efficient. Pour ce type de données, envisagez de créer un index filtré sur une valeur distincte qui se produit uniquement dans un petit nombre de lignes.For this type of data, consider creating a filtered index on a distinct value that only occurs in a small number of rows. Par exemple, si la plupart des valeurs sont 0, l'optimiseur de requête peut utiliser un index filtré pour les lignes de données qui contiennent 1.For example, if most of the values are 0, the query optimizer might use a filtered index for the data rows that contain 1.

Utiliser des colonnes incluses pour étendre les index non clusterUse Included Columns to Extend Nonclustered Indexes

Vous pouvez étendre la fonctionnalité des index non cluster en ajoutant des colonnes non-clés au niveau feuille de l'index non cluster.You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. L'inclusion de colonnes non-clés permet de créer des index non-cluster qui couvrent davantage de requêtes.By including nonkey columns, you can create nonclustered indexes that cover more queries. En effet, les colonnes non-clés présentent les avantages suivants :This is because the nonkey columns have the following benefits:

  • Elles peuvent contenir des types de données qui ne sont pas autorisés dans les colonnes de clés d'index.They can be data types not allowed as index key columns.

  • Elles ne sont pas prises en compte par le Moteur de base de donnéesDatabase Engine lors du calcul du nombre de colonnes clés d'index ou de la taille de la clé d'index.They are not considered by the Moteur de base de donnéesDatabase Engine when calculating the number of index key columns or index key size.

Un index contenant des colonnes non-clés incluses peut améliorer considérablement les performances des requêtes lorsque toutes les colonnes de la requête sont incluses dans l'index en tant que colonnes clés ou non-clés.An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Les gains de performances sont dus au fait que l'optimiseur de requête peut localiser toutes les valeurs des colonnes dans l'index ; l'accès aux données de table et d'index n'a pas lieu, produisant ainsi un nombre moindre d'opérations d'E/S sur le disque.Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

Notes

Lorsqu'un index contient toutes les colonnes auxquelles la requête fait référence, on dit qu'il couvre la requête.When an index contains all the columns referenced by the query it is typically referred to as covering the query.

Alors que les colonnes clés sont stockées à tous les niveaux de l'index, les colonnes non-clés sont stockées uniquement au niveau feuille.While key columns are stored at all levels of the index, nonkey columns are stored only at the leaf level.

Utilisation de colonnes incluses pour éviter les limites de tailleUsing Included Columns to Avoid Size Limits

Vous pouvez inclure des colonnes non-clés dans un index non cluster pour éviter de dépasser les limitations actuelles de taille d'index, établies à 16 colonnes clés au maximum et une taille de clé d'index de 900 octets au maximum.You can include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. Le Moteur de base de donnéesDatabase Engine ne tient pas compte des colonnes non-clés lors du calcul du nombre de colonnes clés d'index ou de la taille de la clé d'index.The Moteur de base de donnéesDatabase Engine does not consider nonkey columns when calculating the number of index key columns or index key size.

Par exemple, supposons que vous voulez indexer les colonnes suivantes de la table Document :For example, assume that you want to index the following columns in the Document table:

Title nvarchar(50)

Revision nchar(5)

FileName nvarchar(400)

Comme les types de données nchar et nvarchar nécessitent deux octets par caractère, un index qui contient ces trois colonnes dépasse de 10 octets (455 * 2) la limitation de taille de 900 octets.Because the nchar and nvarchar data types require 2 bytes for each character, an index that contains these three columns would exceed the 900 byte size limitation by 10 bytes (455 * 2). En utilisant la clause INCLUDE de l'instruction CREATE INDEX , la clé d'index peut être définie en tant que (Title, Revision) et FileName en tant que colonne non-clé.By using the INCLUDE clause of the CREATE INDEX statement, the index key could be defined as (Title, Revision) and FileName defined as a nonkey column. De cette manière, la taille de la clé d’index vaut 110 octets (55 * 2) et l’index contient toujours toutes les colonnes requises.In this way, the index key size would be 110 bytes (55 * 2), and the index would still contain all the required columns. L'instruction ci-dessous crée cet index.The following statement creates such an index.

CREATE INDEX IX_Document_Title   
ON Production.Document (Title, Revision)   
INCLUDE (FileName);   
Directives sur les index contenant des colonnes inclusesIndex with Included Columns Guidelines

Lors de la conception d'index non-cluster contenant des colonnes incluses, tenez compte des directives suivantes :When you design nonclustered indexes with included columns consider the following guidelines:

  • Les colonnes non-clés sont définies dans la clause INCLUDE de l'instruction CREATE INDEX.Nonkey columns are defined in the INCLUDE clause of the CREATE INDEX statement.

  • Les colonnes non-clés peuvent être définies uniquement sur les index non-cluster de tables ou de vues indexées.Nonkey columns can only be defined on nonclustered indexes on tables or indexed views.

  • Tous les types de données sont autorisés, à l'exception de text, ntext et image.All data types are allowed except text, ntext, and image.

  • Les colonnes calculées déterministes et précises ou imprécises peuvent être des colonnes incluses.Computed columns that are deterministic and either precise or imprecise can be included columns. Pour plus d'informations, consultez Indexes on Computed Columns.For more information, see Indexes on Computed Columns.

  • Comme pour les colonnes clés, les colonnes calculées dérivées des types de données image, ntext et text peuvent être des colonnes non-clés (incluses) tant que le type de données de la colonne calculée est autorisé en tant que colonne d'index non-clé.As with key columns, computed columns derived from image, ntext, and text data types can be nonkey (included) columns as long as the computed column data type is allowed as a nonkey index column.

  • Les noms des colonnes ne peuvent pas être spécifiés à la fois dans la liste INCLUDE et dans la liste des colonnes clés.Column names cannot be specified in both the INCLUDE list and in the key column list.

  • Les noms des colonnes ne peuvent pas être répétés dans la liste INCLUDE.Column names cannot be repeated in the INCLUDE list.

Directives sur la taille des colonnesColumn Size Guidelines
  • Vous devez spécifier au moins une colonne clé.At least one key column must be defined. Le nombre maximal de colonnes non-clés est de 1023.The maximum number of nonkey columns is 1023 columns. Il équivaut au nombre maximal de colonnes de table moins 1.This is the maximum number of table columns minus 1.

  • Les colonnes de clés d'index, colonnes non-clés exclues, doivent respecter les restrictions existantes de taille d'index, à savoir 16 colonnes clés au maximum et une taille totale de clé d'index de 900 octets.Index key columns, excluding nonkeys, must follow the existing index size restrictions of 16 key columns maximum, and a total index key size of 900 bytes.

  • La taille totale de toutes les colonnes non-clés est limitée uniquement par la taille des colonnes spécifiées dans la clause INCLUDE ; par exemple, les colonnes varchar(max) sont limitées à 2 Go.The total size of all nonkey columns is limited only by the size of the columns specified in the INCLUDE clause; for example, varchar(max) columns are limited to 2 GB.

Directives sur la modification des colonnesColumn Modification Guidelines

Lors de la définition d'une colonne de table définie en tant que colonne incluse, les restrictions suivantes s'appliquent :When you modify a table column that has been defined as an included column, the following restrictions apply:

  • Les colonnes non-clés ne peuvent pas être supprimées de la table, sauf si l'index est d'abord supprimé.Nonkey columns cannot be dropped from the table unless the index is dropped first.

  • Les colonnes non-clés ne peuvent pas être modifiées, sauf pour effectuer les opérations suivantes :Nonkey columns cannot be changed, except to do the following:

    • modifier la possibilité de valeur NULL de la colonne de NOT NULL à NULL ;Change the nullability of the column from NOT NULL to NULL.

    • augmenter la longueur des colonnes varchar, nvarchar ou varbinary.Increase the length of varchar, nvarchar, or varbinary columns.

      Notes

      Ces restrictions sur la modification des colonnes s'appliquent également aux colonnes de clés d'index.These column modification restrictions also apply to index key columns.

Recommandations relatives à la conceptionDesign Recommendations

La conception d'index non-cluster doit être réalisée avec une clé d'index de grande taille, de sorte que seules les colonnes utilisées pour la recherche sont les colonnes clés.Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Toutes les autres colonnes qui couvrent la requête doivent être des colonnes non-clés incluses.Make all other columns that cover the query included nonkey columns. De cette manière, vous disposez de toutes les colonnes nécessaires pour couvrir la requête, mais la clé d'index elle-même est petite et efficace.In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

Par exemple, supposons que vous voulez concevoir un index qui couvre la requête ci-dessous.For example, assume that you want to design an index to cover the following query.

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode  
FROM Person.Address  
WHERE PostalCode BETWEEN N'98000' and N'99999';  

Pour couvrir la requête, chaque colonne doit être définie dans l'index.To cover the query, each column must be defined in the index. Même si vous pouviez définir toutes les colonnes en tant que colonnes clés, la taille de clé serait 334 octets.Although you could define all columns as key columns, the key size would be 334 bytes. Comme la seule colonne vraiment utilisée comme critère de recherche est la colonne PostalCode , dont la longueur vaut 30 octets, une meilleure conception d'index définirait PostalCode comme colonne clé et inclurait toutes les autres colonnes comme colonnes non-clés.Because the only column actually used as search criteria is the PostalCode column, having a length of 30 bytes, a better index design would define PostalCode as the key column and include all other columns as nonkey columns.

L'instruction suivante crée un index contenant des colonnes incluses pour couvrir la requête.The following statement creates an index with included columns to cover the query.

CREATE INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode)  
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
Considérations relatives aux performancesPerformance Considerations

Évitez d'ajouter des colonnes superflues.Avoid adding unnecessary columns. L'ajout de trop nombreuses colonnes d'index, clés et non-clés, peut avoir les conséquences suivantes sur les performances :Adding too many index columns, key or nonkey, can have the following performance implications:

  • Le nombre de lignes d'index contenues sur une page sera moindre.Fewer index rows will fit on a page. Ceci pourrait augmenter les E/S et réduire l'efficacité de la mémoire cache.This could create I/O increases and reduced cache efficiency.

  • L'espace disque requis pour stocker l'index sera supérieur.More disk space will be required to store the index. En particulier, l'ajout des types de données varchar(max), nvarchar(max), varbinary(max) ou xml en tant que colonnes d'index non-clés peut accroître considérablement l'espace disque nécessaire.In particular, adding varchar(max), nvarchar(max), varbinary(max), or xml data types as nonkey index columns may significantly increase disk space requirements. En effet, les valeurs des colonnes sont copiées dans le niveau feuille de l'index.This is because the column values are copied into the index leaf level. Par conséquent, elles résident à la fois dans l'index et dans la table de base.Therefore, they reside in both the index and the base table.

  • La maintenance d'un index peut accroître la durée nécessaire pour effectuer des modifications, des insertions, des mises à jour ou des suppressions à la table sous-jacente ou à la vue indexée.Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.

Vous devez déterminer si les gains de performances des requêtes compensent la dégradation des performances lors de la modification des données et la quantité d'espace disque supplémentaire nécessaire.You will have to determine whether the gains in query performance outweigh the affect to performance during data modification and in additional disk space requirements.

Icône de flèche utilisée avec le lien Retour au début dans ce GuideArrow icon used with Back to Top link In This Guide

Instructions de conception d'index uniquesUnique Index Design Guidelines

Un index unique garantit que la clé d'index ne contient aucune valeur dupliquée et que, par conséquent, chaque ligne de la table est unique d'une certaine manière.A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Spécifier un index unique n'a de sens que si l'unicité est une caractéristique des données elles-mêmes.Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. Par exemple, si vous souhaitez que les valeurs de la colonne NationalIDNumber de la table HumanResources.Employee soient uniques, lorsque la clé primaire est EmployeeID, créez une contrainte UNIQUE sur la colonne NationalIDNumber .For example, if you want to make sure that the values in the NationalIDNumber column in the HumanResources.Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumber column. Si l'utilisateur essaie de saisir la même valeur dans cette colonne pour plusieurs employés, un message d'erreur apparaît et la valeur dupliquée n'est pas entrée.If the user tries to enter the same value in that column for more than one employee, an error message is displayed and the duplicate value is not entered.

Lorsque vous utilisez un index unique multicolonne, celui-ci garantit que chaque combinaison de valeurs dans la clé d'index est unique.With multicolumn unique indexes, the index guarantees that each combination of values in the index key is unique. Par exemple, si un index unique est créé sur une combinaison des colonnes LastName, FirstNameet MiddleName , deux lignes de la table ne peuvent pas posséder la même combinaison de valeurs pour ces colonnes.For example, if a unique index is created on a combination of LastName, FirstName, and MiddleName columns, no two rows in the table could have the same combination of values for these columns.

Tant les index cluster que les index non-cluster peuvent être uniques.Both clustered and nonclustered indexes can be unique. À condition que les données contenues dans la colonne soient uniques, vous pouvez créer à la fois un index cluster unique et plusieurs index non-cluster uniques sur la même table.Provided that the data in the column is unique, you can create both a unique clustered index and multiple unique nonclustered indexes on the same table.

Les index uniques présentent les avantages suivants :The benefits of unique indexes include the following:

  • L'intégrité des données des colonnes définies est garantie.Data integrity of the defined columns is ensured.

  • L'optimiseur de requête dispose d'informations utiles supplémentaires.Additional information helpful to the query optimizer is provided.

Lorsque vous créez une contrainte PRIMARY KEY ou UNIQUE, vous créez automatiquement un index unique sur les colonnes spécifiées.Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns. Il n'y a pas de différences significatives entre la création d'une contrainte UNIQUE et la création d'un index unique indépendant d'une contrainte.There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. La validation des données se produit de la même manière et l'optimiseur de requête considère un index unique de la même façon, qu'il soit créé par une contrainte or manuellement.Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or manually created. Toutefois, vous devez créer une contrainte UNIQUE ou PRIMARY KEY sur la colonne lorsque votre objectif est de préserver l'intégrité des données.However, you should create a UNIQUE or PRIMARY KEY constraint on the column when data integrity is the objective. Cette opération met en évidence la finalité de l'index.By doing this the objective of the index will be clear.

ObservationsConsiderations

  • Un index unique, une contrainte UNIQUE ou une contrainte PRIMARY KEY ne peuvent pas être créés si les données comportent des valeurs de clé dupliquées.A unique index, UNIQUE constraint, or PRIMARY KEY constraint cannot be created if duplicate key values exist in the data.

  • Si les données sont uniques et que l'unicité doit être assurée, la création d'un index unique au lieu d'un index non unique sur la même combinaison de colonnes fournit des informations supplémentaires à l'optimiseur de requête, qui peut générer des plans d'exécution plus efficaces.If the data is unique and you want uniqueness enforced, creating a unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that can produce more efficient execution plans. La création d'un index unique (si possible en créant une contrainte UNIQUE) est recommandée dans ce cas.Creating a unique index (preferably by creating a UNIQUE constraint) is recommended in this case.

  • Un index non cluster unique peut contenir des colonnes non-clés incluses.A unique nonclustered index can contain included nonkey columns. Pour plus d'informations, consultez Index avec colonnes incluses.For more information, see Index with Included Columns.

Icône de flèche utilisée avec le lien Retour au début dans ce GuideArrow icon used with Back to Top link In This Guide

Instructions de conception d'index filtrésFiltered Index Design Guidelines

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 de données bien défini.A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. Il utilise un prédicat de filtre pour indexer une partie des lignes de la table.It uses a filter predicate to index a portion of rows in the table. Un index filtré bien conçu peut améliorer les performances des requêtes, réduire les coûts de maintenance des index et réduire les coûts de stockage des index par rapport aux index de table entière.A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

S'applique à: SQL Server 2008SQL Server 2008 jusqu'à SQL Server 2014SQL Server 2014.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2014SQL Server 2014.

Les index filtrés peuvent présenter les avantages suivants par rapport aux index de table entière :Filtered indexes can provide the following advantages over full-table indexes:

  • Meilleures performances des requêtes et qualité de plan amélioréeImproved query performance and plan quality

    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.A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. 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é.The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

  • Coûts réduits de maintenance des indexReduced index maintenance costs

    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.An index is maintained only when data manipulation language (DML) statements affect the data in the 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.A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is affected. Il est possible d'avoir un grand nombre d'index filtrés, notamment s'ils contiennent des données qui sont rarement affectées.It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. 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.Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics.

  • Coûts réduits de stockage des indexReduced index storage costs

    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.Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. Vous pouvez remplacer un index non cluster de table entière par plusieurs index filtrés sans augmenter considérablement le stockage nécessaire.You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

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.Filtered indexes are useful when columns contain well-defined subsets of data that queries reference in SELECT statements. Exemples :Examples are:

  • Colonnes éparses qui contiennent uniquement quelques valeurs non NULL.Sparse columns that contain only a few non-NULL values.

  • Colonnes hétérogènes qui contiennent des catégories de données.Heterogeneous columns that contain categories of data.

  • Colonnes qui contiennent des plages de valeurs, telles que des montants en devise, des heures et des dates.Columns that contain ranges of values such as dollar amounts, time, and dates.

  • Partitions de table définies par une logique de comparaison simple pour les valeurs de colonne.Table partitions that are defined by simple comparison logic for column values.

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.Reduced maintenance costs for filtered indexes are most noticeable when the number of rows in the index is small compared with a full-table index. 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.If the filtered index includes most of the rows in the table, it could cost more to maintain than a full-table index. Dans ce cas, vous devez utiliser un index de table entière à la place d'un index filtré.In this case, you should use a full-table index instead of a filtered index.

Les index filtrés sont définis sur une seule table et ne prennent en charge que les opérateurs de comparaison simples.Filtered indexes are defined on one table and only support simple comparison operators. 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.If you need a filter expression that references multiple tables or has complex logic, you should create a view.

Remarques sur la conceptionDesign Considerations

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.In order to design effective filtered indexes, it is important to understand what queries your application uses and how they relate to subsets of your data. 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.Some examples of data that have well-defined subsets are columns with mostly NULL values, columns with heterogeneous categories of values and columns with distinct ranges of values. 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.The following design considerations give a variety of scenarios for when a filtered index can provide advantages over full-table indexes.

Index filtrés pour des sous-ensembles de donnéesFiltered Indexes for Subsets of Data

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.When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. 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.For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. 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.The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

Par exemple, la base de données AdventureWorks2012 a une table Production.BillOfMaterials avec 2679 lignes.For example, the AdventureWorks2012 database has a Production.BillOfMaterials table with 2679 rows. Seules 199 lignes de la colonne EndDate contiennent une valeur non NULL ; les 2480 autres contiennent des valeurs NULL.The EndDate column has only 199 rows that contain a non-NULL value and the other 2480 rows contain 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.The following filtered index would cover queries that return the columns defined in the index and that select only rows with a non-NULL value for EndDate.

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.The filtered index FIBillOfMaterialsWithEndDate is valid for the following query. 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é.You can display the query execution plan to determine if the query optimizer used the filtered index.

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

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 Filtered Indexes.For more information about how to create filtered indexes and how to define the filtered index predicate expression, see Create Filtered Indexes.

Index filtrés pour les données hétérogènesFiltered Indexes for Heterogeneous Data

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.When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data.

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).For example, the products listed in the Production.Product table are each assigned to a ProductSubcategoryID, which are in turn associated with the product categories Bikes, Components, Clothing, or 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.These categories are heterogeneous because their column values in the Production.Product table are not closely correlated. Par exemple, les colonnes Color, ReorderPoint, ListPrice, Weight, Classet Style ont des caractéristiques uniques pour chaque catégorie de produit.For example, the columns Color, ReorderPoint, ListPrice, Weight, Class, and Style have unique characteristics for each product category. Supposons que des requêtes portent fréquemment sur la catégorie Accessories qui comporte les sous-catégories 27-36.Suppose that there are frequent queries for accessories which have subcategories between 27 and 36 inclusive. Améliorez les performances des requêtes portant sur Accessories en créant un index filtré sur les sous-catégories de la catégorie Accessories, tel que l'illustre l'exemple suivant.You can improve the performance of queries for accessories by creating a filtered index on the accessories subcategories as shown in the following example.

CREATE NONCLUSTERED INDEX FIProductAccessories  
    ON Production.Product (ProductSubcategoryID, ListPrice)   
        Include (Name)  
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;

L'index filtré FIProductAccessories couvre la requête suivante, car les résultats de la requêteThe filtered index FIProductAccessories covers the following query because the query

sont contenus dans l'index et le plan de requête n'inclut pas de recherche de table de base.results are contained in the index and the query plan does not include a base table lookup. 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.For example, the query predicate expression ProductSubcategoryID = 33 is a subset of the filtered index predicate ProductSubcategoryID >= 27 and ProductSubcategoryID <= 36, the ProductSubcategoryID and ListPrice columns in the query predicate are both key columns in the index, and name is stored in the leaf level of the index as an included column.

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

Colonnes clésKey Columns

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.It is a best practice to include a small number of key or included columns in a filtered index definition, and to incorporate only the columns that are necessary for the query optimizer to choose the filtered index for the query execution plan. L'optimiseur de requête peut choisir un index filtré pour la requête, qu'il couvre ou non la requête.The query optimizer can choose a filtered index for the query regardless of whether it does or does not cover the query. Toutefois, l'optimiseur de requête choisira plus probablement un index filtré s'il couvre la requête.However, the query optimizer is more likely to choose a filtered index if it covers the query.

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é.In some cases, a filtered index covers the query without including the columns in the filtered index expression as key or included columns in the filtered index definition. 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é.The following guidelines explain when a column in the filtered index expression should be a key or included column in the filtered index definition. Les exemples font référence à l'index filtré FIBillOfMaterialsWithEndDate qui a été créé précédemment.The examples refer to the filtered index, FIBillOfMaterialsWithEndDate that was created previously.

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.A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results. 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.For example, FIBillOfMaterialsWithEndDate covers the following query because the query predicate is equivalent to the filter expression, and EndDate is not returned with the query results. FIBillOfMaterialsWithEndDate n'a pas besoin de la colonne EndDate comme colonne clé ou incluse dans la définition de l'index filtré.FIBillOfMaterialsWithEndDate does not need EndDate as a key or included column in the filtered index definition.

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

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é.A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression. 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é.For example, FIBillOfMaterialsWithEndDate is valid for the following query because it selects a subset of rows from the filtered index. 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é.However, it does not cover the following query because EndDate is used in the comparison EndDate > '20040101', which is not equivalent to the filtered index expression. Le processeur de requête ne peut pas exécuter cette requête sans rechercher les valeurs de EndDate.The query processor cannot execute this query without looking up the values of EndDate. Par conséquent, EndDate doit être une colonne clé ou incluse dans la définition de l'index filtré.Therefore, EndDate should be a key or included column in the filtered index definition.

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

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.A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set. 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.For example, FIBillOfMaterialsWithEndDate does not cover the following query because it returns the EndDate column in the query results. Par conséquent, EndDate doit être une colonne clé ou incluse dans la définition de l'index filtré.Therefore, EndDate should be a key or included column in the filtered index definition.

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

Il n'est pas nécessaire que la clé de l'index cluster de la table soit une colonne clé ou incluse dans la définition de l'index filtré.The clustered index key of the table does not need to be a key or included column in the filtered index definition. La clé de l'index cluster est automatiquement incluse dans tous les index non cluster, y compris les index filtrés.The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.

Opérateurs de conversion de données dans le prédicat du filtreData Conversion Operators in the Filter Predicate

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.If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. 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.A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.

L'exemple suivant crée une table avec différents types de données.The following example creates a table with a variety of data types.

USE AdventureWorks2012;  
GO  
CREATE TABLE dbo.TestTable (a int, b varbinary(4));  

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.In the following filtered index definition, column b is implicitly converted to an integer data type for the purpose of comparing it to the constant 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é.This generates error message 10611 because the conversion occurs on the left hand side of the operator in the filtered predicate.

CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)  
WHERE b = 1;  

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 :The solution is to convert the constant on the right hand side to be of the same type as column b, as seen in the following example:

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

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.Moving the data conversion from the left side to the right side of a comparison operator might change the meaning of the 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.In the above example, when the CONVERT operator was added to the right side, the comparison changed from an integer comparison to a varbinary comparison.

Icône de flèche utilisée avec le lien Retour au début dans ce GuideArrow icon used with Back to Top link In This Guide

Lecture supplémentaireAdditional Reading

Amélioration des performances avec les vues indexées SQL Server 2008Improving Performance with SQL Server 2008 Indexed Views

Partitioned Tables and IndexesPartitioned Tables and Indexes