Partitioned Tables and Indexes in SQL Server 2005

Tables et index partitionnés dans SQL Server 2005
Kimberly L. Tripp
Fondatrice, SQLskills.com
Janvier 2005
Concerne :
SQL Server 2005
Résumé : Dans SQL Server 2005, les fonctions de partitionnement des tables et index offrent flexibilité et
performances et simplifient la création et la maintenance de telles tables. Découvrez les nouvelles fonctions de
partitionnement des tables, et découvrez pourquoi, quand et comment concevoir, implémenter et gérer des
tables partitionnées à l'aide de SQL Server 2005. (41 pages imprimées)
À propos de cet article
Les fonctions et plans décrits dans le présent document reposent sur les dernières
versions préliminaires de SQL Server 2005. Ils ne constituent pas les spécifications de ce produit, et sont sujets
à modifications. Il n'existe aucune garantie, implicite ou autre, que ces fonctions soient intégrées à la version
finale du produit.
Pour certaines fonctions, ce document part du principe que le lecteur connaît bien les fonctions et services de
SQL Server 2000. Pour plus d'informations, visitez le site Web de SQL Server ou reportez-vous au Kit de
ressources SQL Server 2000.
Il ne s'agit pas d'une spécification de produit.
Téléchargez l'exemple de code associé, SQL2005PartitioningScripts.exe.
Sommaire
Pourquoi partitionner ?
Historique du partitionnement
Partitionnement d'objets dans les versions antérieures à SQL Server 7.0
Vues partitionnées dans SQL Server 7.0
Vues partitionnées dans SQL Server 2000
Vues partitionnées dans SQL Server 2005
Définitions et terminologie
Partitions par spécification de plages de valeurs
Définition de la clé de partitionnement
Partitionnement d'index
Conditions spéciales pour les partitions : fractionnement, fusion et aiguillage
Procédure de création de tables partitionnées
Déterminer si des objets doivent être partitionnés
Déterminer la clé de partitionnement et le nombre de partitions
Déterminer si plusieurs groupes de fichiers doivent être utilisés
Créer des groupes de fichiers
Créer la fonction de partition d'une partition par spécification de plages de valeurs
Créer le schéma de partition
Créer la table partitionnée
Créer les index : partitionnés ou non ?
Synthèse : études de cas
Partitionnement par spécification de plages de valeurs : données de vente
Jointure de tables partitionnées
Scénario de fenêtre dynamique
Partitionnement par spécification de listes : données régionales
Résumé
Scripts utilisés dans cet article
Pourquoi partitionner ?
Qu'est-ce que les partitions et pourquoi les utiliser ? La réponse est simple : pour améliorer la capacité à
monter en charge et le mode de gestion des tables volumineuses et des tables à modèles d'accès variables.
Généralement, vous créez des tables pour stocker des informations concernant une entité, comme les clients ou
les ventes, et chaque table possède des attributs décrivant cette entité uniquement. Même s'il est plus facile de
concevoir et de comprendre une table unique pour chaque entité, ces tables ne sont pas forcément optimisées
pour les performances, la montée en charge et le mode de gestion, en particulier lorsque la volumétrie de la
table augmente.
Qu'est-ce qui caractérise une table volumineuse ? Alors que la taille d'une base de données très volumineuse
(VLDB) est exprimée en téraoctets, l'expression « table volumineuse » ne fait pas forcément référence à la
taille des tables individuelles dans la base de données. Une table peut être considérée comme volumineuse si
les activités des utilisateurs ou les opérations de maintenance ont un impact restrictif sur la disponibilité. Par
exemple, la table des ventes est considérée comme volumineuse si les performances sont fortement
détériorées ou si la table est inaccessible pendant deux heures tous les jours, toutes les semaines ou même
tous les mois en raison d'opérations de maintenance. Dans certains cas, un arrêt périodique est acceptable,
mais il peut souvent être évité ou réduit grâce à la mise en œuvre d'une meilleure conception et d'un
partitionnement plus approprié. Même si l'expression « base de données très volumineuse (VLDB) » ne
s'applique qu'à une base de données, pour ce qui concerne le partitionnement, il est plus important d'examiner
la taille des tables.
Outre la taille, une table à modèles d'accès variables peut être source d'inquiétude en matière de performances
et de disponibilité lorsque différents ensembles de lignes dans la table sont associés à différents modèles
d'utilisation. Même si les modèles d'utilisation ne sont pas toujours variables (et cela ne constitue pas une
exigence pour le partitionnement), lorsque les structures d'utilisation varient, le partitionnement peut permettre
de bénéficier de gains supplémentaires en termes de gestion, de performances et de disponibilité. De même,
pour utiliser l'exemple d'une table de ventes, les données du mois courant peuvent être accessibles en lectureécriture, tandis que celles du mois précédent (souvent la partie la plus importante de la table) sont accessibles
en lecture seule. Dans ce cas, lorsque l'utilisation des données varie ou lorsque les charges de maintenance
sont considérables du fait de l'entrée et de la sortie des données dans la table, la capacité de la table à
répondre aux demandes des utilisateurs peut en pâtir. Cet état de fait limite la disponibilité et la montée en
charge du serveur.
De plus, lorsque des ensembles volumineux de données sont utilisés de différentes manières, des opérations de
maintenance fréquentes sont effectuées sur les données statiques. Ceci peut s'avérer coûteux engendrer des
problèmes de performances , des blocages des sauvegardes (problèmes d'espace, de temps et d'exploitation)
et avoir un impact négatif sur la montée en charge du serveur.
En quoi le partitionnement peut-il être utile ? Lorsque les tables et les index deviennent très volumineux, le
partitionnement peut permettre de partitionner les données en sections plus petites et plus faciles à gérer. Cet
article traite du partitionnement horizontal, dans lequel des groupes de lignes importants sont stockés en
plusieurs partitions distinctes. La définition de l'ensemble partitionné est personnalisée, définie et gérée en
fonction de vos besoins. Microsoft SQL Server 2005 vous permet de partitionner les tables en fonction de
modèles d'utilisation des données spécifiques à l'aide de plages de valeurs ou de listes définies. SQL
Server 2005 propose également de nombreuses options pour la gestion à long terme de tables et d'index
partitionnés grâce à l'ajout de fonctions conçues autour de la nouvelle structure de table et d'index.
De plus, s'il existe une table volumineuse sur un système comportant plusieurs processeurs, le partitionnement
de la table permet l'amélioration des performances grâce aux opérations parallèles. Les performances des
opérations de grande envergure sur des volumétries extrêmement volumineuses (par exemple, des millions de
lignes) bénéficient de la réalisation de ces opérations en parallèle sur des sous-ensembles plus petits. Les
agrégations disponibles dans les versions précédentes constituent un exemple de gains de performances au
niveau des partitions. Ainsi, au lieu d'agréger une table volumineuse unique, SQL Server peut fonctionner
indépendamment sur des partitions, puis agréger les agrégations. Dans SQL Server 2005, les requêtes qui
joignent des volumétries de données importantes peuvent bénéficier directement du partitionnement. SQL
Server 2000 prend en charge les opérations de jointure parallèles sur des sous-ensembles, mais implique
encore la création de sous-ensembles à la volée. Dans SQL Server 2005, les tables associées (comme les tables
Order et OrderDetails), qui sont partitionnées à l'aide de la même clé de partitionnement et de la même
fonction de partition sont dites alignées. Lorsque l'optimiseur détecte que deux tables partitionnées et alignées
sont jointes, SQL Server 2005 peut joindre les données qui se trouvent dans les mêmes partitions, puis
combiner les résultats. Cela permet à SQL Server 2005 d'utiliser avec plus d'efficacité des ordinateurs multiprocesseurs.
Historique du partitionnement
Le concept de partitionnement n'est pas nouveau dans SQL Server. En fait, chaque version du produit autorise
déjà des formes de partitionnement. Cependant, sans fonctions conçues spécifiquement pour permettre de
créer et de gérer un schéma de partition, le partitionnement a souvent été une opération lourde et sousutilisée. De plus, les utilisateurs et les développeurs ne comprenant pas le schéma (en raison d'une conception
plus complexe de la base de données), les avantages étaient réduits. Toutefois, en raison de gains de
performances considérables inhérents au concept, SQL Server 7.0 a commencé à améliorer la fonction en
permettant des formes de partitionnement par le biais des vues partitionnées. SQL Server 2005 offre désormais
les plus importantes avancées en matière de partitionnement d’ensemble de données volumineux par le biais
des tables partitionnées.
Partitionnement d'objets dans les versions antérieures à SQL Server 7.0
Dans SQL Server 6.5 et les versions antérieures, le partitionnement devait faire partie de la conception et être
intégré à l'intégralité du code d'accès aux données et aux pratiques de transmission de requêtes. En créant
plusieurs tables, puis en gérant l'accès aux tables appropriées grâce aux procédures stockées, aux vues ou aux
applications client, il était souvent possible d'améliorer les performances de certaines opérations, mais cela se
traduisait par une plus grande complexité à la conception. Les utilisateurs et les développeurs devaient
connaître (et référencer correctement) les tables appropriées. Chaque partition était créée et gérée
distinctement et des vues étaient utilisées pour simplifier l'accès. Cependant, cette solution n’était pas très
productive en termes de performances. Lorsqu'il existait une vue unifiée pour simplifier l'accès des utilisateurs
et des applications, le processeur de requêtes devait accéder à chaque table sous-jacente afin de déterminer les
données nécessaires à l'ensemble de résultats. Si seul un nombre réduit de sous-ensembles des tables sousjacentes était nécessaire, chaque utilisateur et développeur devait connaître le schéma des données afin de ne
référencer que les tables appropriées.
Vues partitionnées dans SQL Server 7.0
Les défis mis en évidence par la création manuelle de partitions dans les versions antérieures à SQL Server 7.0
portaient principalement sur les performances. Alors que les vues simplifiaient la conception d'applications,
l'accès des utilisateurs et la rédaction de requêtes, elles n'offraient pas de gains en matière de performances.
Avec la publication de SQL Server 7.0, les vues ont été combinées à des contraintes pour permettre à
l'optimiseur de requêtes de supprimer les tables inappropriées du plan de requêtes (c'est-à-dire, de supprimer
certaines partitions) et de réduire ainsi considérablement le coût global du plan lorsqu'une vue unifiée accédait
à plusieurs tables.
Observez la vue yearlySales de la figure 1. Au lieu d'avoir plusieurs ventes dans une table volumineuse unique,
vous pouvez définir douze tables individuelles (SalesJanuary2003, SalesFebruary2003, etc.), puis des vues
de chaque trimestre ainsi qu'une vue de l'année entière, YearlySales.
Figure 1 - Vues partitionnées dans SQL Server 7.0/2000.
Les utilisateurs qui accèdent à la vue YearlySales avec la requête ci-dessous sont dirigés uniquement vers la
table SalesJanuary2003.
SELECT ys.*
FROM dbo.YearlySales AS ys
WHERE ys.SalesDate = '20030113'
Tant que les contraintes sont approuvées et que les requêtes dans la vue utilisent une clause WHERE pour
restreindre les résultats en fonction de la clé de partition (la colonne dans laquelle la contrainte est définie),
SQL Server accède uniquement à la table de base de données nécessaire. Les contraintes approuvées sont des
contraintes dans lesquelles SQL Server peut garantir que toutes les données correspondent aux propriétés
définies par la contrainte. Lorsque la contrainte est créée, le comportement par défaut consiste à créer la
contrainte WITH CHECK. Ce paramètre entraîne l'utilisation d'un verrou de structure dans la table afin que les
données puissent être vérifiées en les comparant à la contrainte. Une fois que la vérification valide les données
existantes, la contrainte est ajoutée. Une fois que le verrou de structure est supprimé, les insertions, mises à
jour et suppressions qui suivent doivent respecter la contrainte en vigueur. En utilisant cette procédure pour
créer des contraintes approuvées, les développeurs peuvent réduire considérablement la complexité de leur
code d’accès à l'aide de vues sans avoir à accéder directement à la table qui les intéresse (ni même à connaître
celle-ci). Avec les contraintes approuvées, SQL Server améliore les performances en supprimant les tables
inutiles du plan d'exécution.
Remarque Une contrainte peut devenir « désapprouvée » de différentes manières. Par exemple, si une
insertion en bloc est effectuée sans indiquer l'argument CHECK_CONSTRAINTS ou si une contrainte est créée
avec l'élément NOCHECK. Si une contrainte est désapprouvée, le processeur de requêtes recommence à
analyser toutes les tables de base de données puisqu'il n'a pas de moyen de vérifier que les données
demandées se trouvent en fait dans la table correcte.
Vues partitionnées dans SQL Server 2000
Même si SQL Server 7.0 a considérablement simplifié la conception et amélioré les performances pour les
instructions SELECT, cette version n'a pas proposé d’améliorations pour ce qui est des instructions de
modification de données. Les instructions INSERT, UPDATE et DELETE étaient prises en charge uniquement
dans les tables de base de données et non directement dans les vues qui unifiaient les tables. Dans SQL
Server 2000, les instructions de modification de données tirent également profit des fonctions de vue
partitionnée introduites dans SQL Server 7.0. Dans la mesure où les instructions de modification de données
peuvent utiliser la même structure de vues partitionnées, SQL Server peut diriger les modifications vers la table
de base de données appropriée par le biais de la vue. Des restrictions concernant la clé de partitionnement et
sa création sont nécessaires à une utilisation adéquate. Cependant, les principes de base sont identiques dans
le sens où non seulement les requêtes SELECT sont envoyées directement vers les tables de base de données
appropriées, mais les modifications le sont aussi. Pour plus d'informations sur les restrictions, la configuration
ou les recommandations en matière de partitionnement dans SQL Server 2000, reportez-vous à la section Using
Partitions in a Microsoft SQL Server 2000 Data Warehouse (Utilisation de partitions dans un entrepôt de
données Microsoft SQL Server 2000) disponible en anglais.
Tables partitionnées dans SQL Server 2005
Alors que les améliorations apportées à SQL Server 7.0 et à SQL Server 2000 ont permis d'augmenter
considérablement les performances lors de l'utilisation de vues partitionnées, elles n'ont pas simplifié
l'administration, la conception ou le développement de données partitionnées. Lors de l'utilisation de vues
partitionnées, toutes les tables de la base de données (sur lesquelles la vue est définie) doivent être créées et
gérées individuellement. La conception d'applications est facilitée et les utilisateurs en tirent parti dans le sens
où ils n'ont pas besoin de savoir à quelle table de la base de données ils doivent accéder directement, mais
l'administration reste complexe lorsqu'il y a de nombreuses tables à gérer et que les contraintes d'intégrité des
données doit être gérées pour chaque table. En raison des problèmes de gestion, les vues partitionnées étaient
jusqu'à présent souvent utilisées pour distinguer des tables uniquement lorsque des données devaient être
archivées ou chargées. Lorsque les données étaient déplacées vers la table en lecture seule ou supprimées de
la table en lecture seule, les opérations étaient coûteuses (en temps et en espace) et entraînaient souvent des
verrouillages.
De plus, comme les stratégies de partitionnement dans les versions précédentes exigeaient du développeur
qu'il crée des tables et des index individuels et les unissent grâce à des vues, l'optimiseur devait valider et
déterminer des plans pour chaque partition (en raison de la possibilité de variation des index). C'est la raison
pour laquelle le temps d'optimisation des requêtes dans SQL Server 2000 augmente de façon linéaire avec le
nombre de partitions traitées.
Dans SQL Server 2005, chaque partition possède les mêmes index par définition. Par exemple, imaginez un
scénario dans lequel les données de traitement transactionnel (OLTP) du mois courant doivent être déplacées
vers une table d'analyse à la fin de chaque mois. La table d'analyse (à utiliser pour les requêtes en lecture
seule) est une table simple avec un index cluster et deux index non cluster. Le chargement en bloc de 1 gigaoctet (Go) (dans la table simple déjà indexée et active) crée un verrouillage avec les utilisateurs actuels lorsque
la table et /ou les index sont fragmentés et/ou verrouillés. En outre, le processus de chargement prend un
temps important, car la table et les index doivent être gérés à chaque ajout de ligne. Il existe des moyens
d'accélérer le chargement en bloc. Ceux-ci peuvent affecter directement tous les autres utilisateurs et favoriser
la vitesse au détriment de la simultanéité.
Si ces données étaient isolées dans une nouvelle table (vide) non indexée [heap], le chargement pourrait avoir
lieu en premier puis les index seraient créés après le chargement des données. Grâce à ce schéma, vous
pourriez multiplier souvent vos performances par dix ou davantage. En fait, en procédant au chargement dans
une table non indexée, vous pouvez tirer parti des différents processeurs en chargeant plusieurs fichiers de
données en parallèle ou en chargeant plusieurs segments à partir du même fichier (défini par la position de la
ligne de début et de la ligne de fin). Dans la mesure où les deux opérations peuvent bénéficier du parallélisme,
cela peut entraîner d’importants gains en matière de performances.
Dans toutes les versions de SQL Server, le partitionnement permet un contrôle avec une granularité plus fine et
ne nécessite pas que toutes les données se trouvent à un seul endroit. Cependant, de nombreux objets doivent
être créés et gérés. Une stratégie de partitionnement fonctionnel était possible dans les versions précédentes
en créant et en supprimant dynamiquement des tables, et en modifiant la vue unifiée. Cependant, dans SQL
Server 2005, la solution est plus simple : Il suffit d'insérer les partitions qui viennent d'être remplies sous forme
de partitions supplémentaires du schéma de partition existant et de retirer les anciennes partitions. De bout en
bout, le processus prend peu de temps et peut être exécuté avec plus d'efficacité en utilisant le chargement en
bloc et la création d'index en parallèle. Plus important, la partition est manipulée en dehors de la portée de la
table afin qu'il n'y ait pas d'impact sur la table dans laquelle les requêtes sont effectuées tant que la partition
n'a pas été ajoutée. De ce fait, l'ajout d'une partition ne prend généralement que quelques secondes.
L'amélioration des performances s'avère également notable lorsque des données doivent être supprimées. Si
une base de données à besoin d'un ensemble de données dans une fenêtre dynamique dans laquelle les
nouvelles données ont migrées (par exemple, le mois courant) et que les anciennes données sont supprimées
(peut-être le même mois de l'année précédente), les performances de cette migration de données peuvent être
améliorées d’un ordre de magnitude en ayant recours au partitionnement. Même si cela semble extrême,
envisagez la différence de scénario avec et sans le partitionnement. Lorsque toutes les données se trouvent
dans une table unique, la suppression de 1 Go d'anciennes données nécessite une manipulation ligne à ligne de
la table et des index associés. Le processus de suppression des données crée un volume d'activité de
journalisation considérable, ne permet pas de tronquer le journal pendant la durée de la suppression (sachez
que si la suppression est une transaction à validation automatique, vous pouvez toutefois contrôler la taille de
la transaction en effectuant plusieurs suppressions lorsque cela est possible) et par conséquent, nécessite un
journal éventuellement plus volumineux. Grâce au partitionnement, cependant, la suppression de ces mêmes
données implique la suppression de la partition spécifique à partir d'une table partitionnée (qui est une
opération sur des métadonnées), puis la suppression ou la troncature de la table autonome.
Par ailleurs, si vous ne savez pas quelle est la meilleure méthode pour concevoir des partitions, peut-être
ignorez-vous que l'utilisation des groupes de fichiers conjointement aux partitions est idéale pour
l'implémentation du partitionnement. Les groupes de fichiers permettent de placer des tables individuelles sur
des disques physiques différents. Si une table unique balaie plusieurs fichiers (à l'aide de groupes de fichiers),
l'emplacement physique des données ne peut pas être prévu. Pour les systèmes dans lesquels aucune opération
parallèle n'est attendue, SQL Server améliore les performances en utilisant tous les disques de manière
équitable dans les groupes de fichiers, ce qui a pour effet de rendre le positionnement spécifique des données
moins critique.
Remarque La figure 2 présente trois fichiers dans un groupe de fichiers unique. Deux tables, Orders
(Commandes) et OrderDetails (DétailsCommande), ont été placées dans ce groupe de fichiers. Lorsque les
tables sont placées dans un groupe de fichiers, SQL Server remplit proportionnellement les fichiers du groupe
de fichiers en utilisant les allocations d'extension (blocs de 64 Ko, équivalant à huit pages de 8 Ko) dans chacun
des fichiers dès que les objets ont besoin d'espace dans les groupes de fichiers. Lorsque les tables Orders et
OrderDetails sont créées, le groupe de fichiers est vide. Lorsqu'une commande arrive, les données sont
entrées dans la table Orders (une ligne par commande) et une ligne par article est entrée dans la table
OrderDetails. SQL Server alloue une extension à la table Orders dans le fichier 1, puis une autre extension à
la table OrderDetails dans le fichier 2. Il est probable que la table OrderDetails grossira plus rapidement que
la table Orders et les allocations qui suivent seront dirigées vers la table suivante qui a besoin d'espace.
Lorsque la table OrderDetails prend de l'importance, elle obtient l'extension suivante du fichier 3 et SQL
Server continue à parcourir les fichiers du groupe de fichiers. Sur la figure 2, suivez chaque table vers une
extension et à partir de chaque extension vers le groupe de fichiers approprié. Les extensions sont allouées en
fonction de l'espace requis. Elles sont numérotées selon le flux.
Figure 2 - Remplissage proportionnel à l'aide de groupes de fichiers.
SQL Server continue à équilibrer les allocations entre tous les objets de ce groupe de fichiers. Même si SQL
Server est exécuté avec plus d'efficacité lorsque davantage de disques sont utilisés pour une opération donnée,
l'utilisation d'un plus grand nombre de disques ne se montre pas aussi optimale du point de vue de la gestion
ou de la maintenance, en particulier pour des modèles d'utilisation très prévisibles (et isolés). Dans la mesure
où les données n'ont pas d'emplacement spécifique sur le disque, vous n'avez pas la possibilité de les isoler
pour la maintenance (opérations de sauvegarde, par exemple).
Avec des tables partitionnées dans SQL Server 2005, une table peut être conçue (à l'aide d'une fonction et d'un
schéma) de sorte que toutes les lignes ayant la même clé de partitionnement soient placées directement à un
emplacement spécifique (et toujours dirigées vers celui-ci). La fonction définit les limites de la partition et
détermine la partition dans laquelle la première valeur doit être placée. Dans le cas d'une fonction de partition
LEFT, la première valeur sert de limite supérieure dans la première partition. Dans le cas d'une fonction de
partition RIGHT, la première valeur sert de limite inférieure dans la seconde partition (les fonctions de partition
sont présentées plus loin dans cet article). Une fois la fonction définie, il est possible de créer un schéma de
partition pour définir le mappage physique des partitions vers leur emplacement dans la base de données, selon
une fonction de partition. Lorsque plusieurs tables utilisent la même fonction (mais pas forcément le même
schéma), les lignes ayant la même clé de partitionnement sont regroupées de manière identique. Ce concept
est appelé alignement. En alignant les lignes ayant la même clé de partitionnement et provenant de plusieurs
tables qui se trouvent sur les mêmes disques physiques ou sur des disques physiques différents, SQL Server
peut, si l'optimiseur le choisit, n'utiliser que les groupes de données nécessaires provenant de chacune des
tables. Pour réaliser l'alignement, il doit exister une certaine correspondance entre les partitions respectives de
deux tables ou index partitionnés. Ces partitions doivent utiliser des fonctions de partition équivalentes par
rapport aux colonnes de partitionnement. Il est possible d'utiliser deux fonctions de partition pour aligner des
données dans les cas suivants :

Les deux fonctions de partition utilisent le même nombre d'arguments et de partitions.

La clé de partitionnement utilisée dans chaque fonction est de même type (notamment la longueur, la
précision et l'échelle, le cas échéant, et le classement, le cas échéant).

Les valeurs de limites sont équivalentes (notamment les critères de limites LEFT/RIGHT).
Remarque Même lorsque deux fonctions de partition sont conçues pour aligner des données, vous pouvez
aboutir à un index non aligné s'il n'est pas partitionné sur la même colonne que la table partitionnée.
La colocation est une forme d'alignement renforcé, dans lequel deux objets alignés sont joints avec un prédicat
d'équi-jointure, dans lequel l'équi-jointure correspond à la colonne de partitionnement. Cela devient important
dans le contexte d'une requête, d'une sous-requête ou d'une autre construction similaire dans laquelle des
prédicats d'équi-jointure peuvent être observés. La colocation est intéressante car les requêtes qui sont jointes
aux tables dans les colonnes de partition sont généralement plus rapides. Reportez-vous aux tables Orders et
OrderDetails décrites dans la figure 2. Au lieu de remplir les fichiers proportionnellement, vous pouvez créer
un schéma de partition qui mappe vers trois groupes de fichiers. Lorsque vous définissez les tables Orders et
OrderDetails, vous les définissez de manière à utiliser le même schéma. Les données associées ayant la
même valeur de clé de partitionnement sont placées dans le même fichier, en isolant les données nécessaires à
la jointure. Lorsque des lignes associées provenant de plusieurs tables sont partitionnées de la même manière,
SQL Server peut joindre les partitions sans avoir à rechercher dans une table entière ou dans plusieurs
partitions (en cas d'utilisation d'une autre fonction de partition par la table) pour les lignes correspondantes.
Dans ce cas, les objets ne sont pas alignés uniquement car ils utilisent la même clé. Leur stockage est aligné
car les mêmes données figurent dans les mêmes fichiers.
La figure 3 indique que deux objets peuvent utiliser le même schéma de partition et toutes les lignes de
données ayant la même clé de partitionnement sont dirigées vers le même groupe de fichiers. Lorsque des
données associées sont alignées, SQL Server peut effectivement fonctionner en parallèle sur des ensembles
volumineux. Par exemple, toutes les données de vente pour le mois de janvier (pour les tables Orders et
OrderDetails) figurent dans le premier groupe de fichiers, celles du mois de février dans le deuxième groupe
de fichiers, etc.
Figure 3 - Tables dont le stockage est aligné.
SQL Server permet le partitionnement à partir de la spécification de plages de valeurs. Il est possible de
concevoir des tables et des index de sorte qu'ils utilisent le même schéma pour un meilleur alignement. Une
bonne conception améliore considérablement les performances globales, mais que se passe-t-il en cas de
variation de l'utilisation des données dans le temps ? Que se passe-t-il si une autre partition est nécessaire ? La
simplicité de l'administration pour ce qui est de l'ajout, de la suppression et de la gestion des partitions en
dehors de la table partitionnée comptait parmi les objectifs de conception de SQL Server 2005.
SQL Server 2005 simplifie le partitionnement sans perdre de vue l'administration, le développement et
l'utilisation. Voici quelques avantages liés aux performances et aux possibilités de gestion :

Simplification de la conception et de l'implémentation de tables volumineuses ayant besoin d'être
partitionnées pour des raisons de performances et de facilité d’administration.

Chargement des données dans une nouvelle partition d'une table partitionnée existante avec un
impact minimal sur l'accès aux données dans les partitions restantes.

Chargement des données dans une nouvelle partition d'une table partitionnée existante avec des
performances correspondant au chargement des mêmes données dans une nouvelle table vide.

Archivage et/ou suppression d'une partie d'une table partitionnée tout en réduisant l'impact sur l'accès
au reste de la table.

Possibilité de gérer les partitions en insérant et en retirant des partitions dans la table partitionnée.

Montée en charge et parallélisme accrus pour les opérations de très grande envergure sur plusieurs
tables associées.

Amélioration des performances sur toutes les partitions.

Amélioration du temps d'optimisation des requêtes car il n'est pas nécessaire d'optimiser chaque
partition individuellement.
Définitions et terminologie
Pour mettre en œuvre des partitions dans SQL Server 2005, vous devez connaître les concepts, les termes et la
syntaxe utilisés. Pour comprendre ces nouveaux concepts, commençons par examiner la structure d'une table
en termes de création et de positionnement. Dans les versions précédentes, une table était toujours soit un
concept physique, soit un concept logique. Avec les tables et les index partitionnés de SQL Server 2005, vous
avez plusieurs possibilités sur la manière et le moment où le stockage d'une table est effectué. Dans SQL
Server 2005, il est possible de créer des tables et des index avec la même syntaxe que dans les versions
précédentes (sous forme de structure tabulaire simple, positionnée dans le groupe de fichiers DEFAULT ou dans
un groupe de fichiers défini par l’utilisateur). De plus, dans SQL Server 2005, les tables et les index peuvent
être créés dans un schéma de partition. Le schéma de partition mappe l'objet vers un ou plusieurs groupes de
fichiers. Pour déterminer les données qui sont dirigées vers des emplacements physiques appropriés, le schéma
de partition utilise une fonction de partition. La fonction de partition définit l'algorithme à utiliser pour diriger
les lignes, tandis que le schéma associe les partitions à l'emplacement physique approprié (c'est-à-dire, un
groupe de fichiers). En d'autres termes, la table est toujours un concept logique mais son positionnement
physique sur le disque peut être tout à fait différent par rapport aux versions précédentes. La table peut
disposer d'un schéma.
Partitions par spécification de plages de valeurs
Les partitions par spécification de plages de valeurs sont des partitions de tables définies par des plages de
données spécifiques et personnalisables. Les limites de la partition par spécification de plages de valeurs sont
choisies par le développeur et peuvent être modifiées lorsque les modèles d'utilisation des données varient. En
général, ces plages de valeurs sont des regroupements de données basés ou triés sur la date.
L'utilisation principale des partitions par plages de valeurs concerne l'archivage des données, l'aide à la décision
(comme c'est souvent le cas, lorsque seules des plages de données spécifiques sont nécessaires, sur un mois
ou un trimestre, par exemple) et pour les systèmes transactionnels en ligne et les systèmes d'aide à la décision
dans lesquels l'utilisation des données varie au cours du cycle de vie d'une ligne. L'avantage majeur d'une table
et d'un index partitionnés SQL Server 2005 réside dans la possibilité de manipuler des plages de données
spécifiques, en particulier pour ce qui est de l'archivage et de la maintenance. Avec les partitions par plages de
valeurs, il est possible d'archiver les anciennes données et de les remettre à disposition très rapidement. Les
partitions par plages de valeurs sont particulièrement adaptées lorsque l'accès aux données se fait
généralement pour l'aide à la décision sur des plages de données étendues. En pareil cas, il est important de
savoir où se trouvent spécifiquement les données afin que seules les partitions appropriées soient accessibles,
lorsque cela est nécessaire. De plus, lorsque les données de transaction sont mises à disposition, vous pouvez
ajouter des données facilement et rapidement. Les partitions par plages de valeurs sont initialement plus
complexes à définir car vous aurez besoin de définir les conditions de limites pour chacune des partitions. Par
ailleurs, vous créez un schéma pour mapper chaque partition vers un ou plusieurs groupes de fichiers.
Cependant, elles suivent souvent un schéma cohérent si bien qu'une fois définies, elles seront susceptibles
d'être facilement gérés par programme (reportez-vous à la figure 4).
Figure 4 - Table contenant des plages de valeurs partitionnées avec 12 partitions.
Définition de la clé de partitionnement
La première étape de partitionnement des tables et des index consiste à définir les données à partir desquelles
la clé de partitionnement est définie. La clé de partitionnement doit exister sous forme de colonne unique dans
la table et doit remplir un certain nombre de critères. La fonction de partition définit le type de données sur
lesquelles la clé (également appelée séparation logique des données) est basée. La fonction définit cette clé
mais pas le positionnement physique des données sur le disque. Le positionnement des données est déterminé
par le schéma de partition. En d'autres termes, le schéma mappe les données vers un ou plusieurs groupes de
fichiers, qui mappent les données vers des fichiers spécifiques et par conséquent, vers des disques. Le schéma
utilise toujours une fonction pour réaliser cette opération : si la fonction définit cinq partitions, le schéma doit
utiliser cinq groupes de fichiers. Il n'est pas nécessaire que les groupes de fichiers soient différents. En
revanche, vous obtiendrez de meilleures performances si vous utilisez plusieurs disques et, de préférence,
plusieurs processeurs. Lorsque le schéma est utilisé avec une table, vous définissez la colonne utilisée comme
argument pour la fonction de partition.
Pour les partitions par plages de valeurs, l’ensemble des données est divisé par une limite logique liée aux
données. L'utilisation des données dicte une partition par plages de valeurs lorsque la table est utilisée dans un
schéma qui définit des limites logiques d'analyse (également appelé plages de valeurs). La clé de
partitionnement pour une fonction de plage de valeurs peut comprendre une seule colonne. La fonction de
partition inclut l'intégralité du domaine, même lorsque les données n'existent pas dans la table (en raison de
l'intégrité des contraintes des données). En d'autres termes, les limites sont définies pour chaque partition,
mais la première et la dernière partition incluent, potentiellement, des lignes pour les valeurs les plus à gauche
(valeurs inférieures à la condition de limite la moins élevée) et pour les valeurs les plus à droite (valeurs
supérieures à la condition de limite la plus élevée). Ainsi, pour restreindre le domaine de valeurs à un ensemble
de données spécifique, les partitions doivent être combinées à l'aide de contraintes CHECK. L'utilisation de
contraintes de vérification pour appliquer des règles métier et de contraintes d'intégrité des donnés permet de
restreindre l’ensemble de données pour former une plage de valeurs finie plutôt qu'une plage de valeurs infinie.
Les partitions par plages de valeurs sont idéales lorsque la maintenance et l'administration impliquent
l'archivage régulier de grandes plages de données et lorsque les requêtes accèdent à une grande quantité de
données qui se trouvent dans un sous-ensemble des plages de valeurs.
Partitionnement d'index
Outre le partitionnement d'une table, vous pouvez partitionner des index. Le partitionnement de la table et de
ses index à l'aide de la même fonction permet souvent d'optimiser les performances. Lorsque les index et la
table utilisent la même fonction de partition et les colonnes dans le même ordre, la table et l'index sont alignés.
Si un index est créé dans une table déjà partitionnée, SQL Server aligne automatiquement le nouvel index sur
le schéma de partition de la table sauf si l'index est explicitement partitionné d'une autre manière. Lorsqu'une
table et ses index sont alignés, SQL Server peut insérer et retirer des partitions dans des tables partitionnées,
avec plus d'efficacité, car toutes les données associées et tous les index sont divisés à l'aide du même
algorithme.
Lorsque les tables et les index sont définis non seulement avec la même fonction de partition, mais également
avec le même schéma de partition, ils sont considérés comme ayant des stockages alignés. L'un des avantages
de cet alignement est que toutes les données ayant la même limite se trouvent sur les mêmes disques
physiques. Dans ce cas, il est possible d'isoler les sauvegardes dans une plage de temps. Les stratégies
peuvent alors varier, en termes de fréquence et de type de sauvegarde, en fonction de la volatilité des
données. Vous pouvez obtenir des gains supplémentaires lorsque les tables et les index du même fichier ou du
même groupe de fichiers sont joints ou agrégés. SQL Server tire parti de l'exécution parallèle d'une opération
sur plusieurs partitions. En cas d'alignement sur le stockage et de plusieurs unités centrales, chaque processeur
peut fonctionner directement sur un fichier ou un groupe de fichiers spécifique sans conflit pour l'accès aux
données car toutes les données nécessaires se trouvent sur le même disque. Cela permet d'exécuter un plus
grand nombre de processus en parallèle sans interruption.
Pour plus d'informations, reportez-vous aux instructions spéciales concernant les index partitionnés dans la
documentation en ligne de SQL Server.
Conditions spéciales pour les partitions : fractionnement, fusion et commutation
Pour vous aider à utiliser les tables partitionnées, il existe de nouvelles fonctions et de nouveaux concepts
associés à la gestion des partitions. Étant donné que les partitions sont utilisées pour les tables volumineuses et
évolutives, le nombre de partitions choisi lorsque la fonction de partition a été créée varie dans le temps. Vous
pouvez utiliser l'instruction ALTER TABLE avec la nouvelle option de fractionnement pour ajouter une autre
partition à la table. Lorsqu'une partition est fractionnée, les données peuvent être déplacées vers la nouvelle
partition. Cependant, pour maintenir les performances, les lignes ne doivent pas être déplacées. Ce scénario est
décrit dans l'étude de cas un plus loin dans cet article.
À l'inverse, pour supprimer une partition, effectuer un retrait des données, puis fusionner le point de limite.
Dans le cas des partitions par plages de valeurs, une demande de fusion est effectuée en spécifiant le point de
limite à supprimer. Lorsque, seule, une période spécifique de données est nécessaire, et que l'archivage des
données est réalisé régulièrement (par exemple, tous les mois), vous avez la possibilité d'archiver une partition
de données (le mois précédent) dès que les données du mois courant sont disponibles. Par exemple, vous
pouvez choisir de garder à disposition une année de données et à la fin de chaque mois vous insérez le mois
courant et retirez le mois précédent, en distinguant le système transactionnel en ligne en lecture/écriture du
mois courant et les données en lecture seule du mois précédent. Il existe un flux spécifique d'actions qui
améliore l'efficacité du processus, comme le montre le scénario qui suit.
Vous conservez une année de données en lecture seule. Actuellement, la table contient les données de
septembre 2003 à août 2004. Le mois de septembre 2004 en cours se trouve dans une autre base de données,
optimisée pour les performances du système transactionnel en ligne. Dans la version en lecture seule de la
table figurent 13 partitions : douze partitions contenant les données (septembre 2003 à août 2004) et une
partition finale qui est vide. Cette dernière partition est vide car une partition par spécification de plages de
valeurs inclut toujours le domaine entier, c'est-à-dire les valeurs les plus à gauche et les valeurs les plus à
droite. Si vous envisagez de gérer les données dans un scénario de fenêtre dynamique, vous aurez toujours
besoin d'une partition vide pour le fractionnement des nouvelles données qui seront positionnées. Dans une
fonction de partition définie avec les points de limite LEFT, la partition vide existe logiquement pour les valeurs
les plus à droite. En laissant une partition vide à la fin, cela vous permet de fractionner cette dernière (pour les
nouvelles données insérées). Il n'est alors pas nécessaire de déplacer les lignes de la dernière partition (car il
n'en existe pas) vers le nouveau groupe de fichiers qui est ajouté (lorsque la partition est fractionnée de
manière à inclure un autre segment de données). Ce concept, relativement complexe, sera traité plus en détail
dans l'étude de cas dans la suite de cet article. Il repose sur L'idée que tous les ajouts et toutes les
suppressions de données doivent être des opérations portant uniquement sur les métadonnées. Pour s'assurer
que les opérations portant uniquement sur les métadonnées ont lieu, vous devez gérer stratégiquement la
partie variable de la table. Pour vous assurer que cette partition est vide, vous utilisez une contrainte de
vérification afin de restreindre ces données dans la table de base de données. Dans ce cas, la valeur
OrderDate ne doit être active qu'à partir du 1er septembre 2003 et avant le 1er septembre 2004. Si le dernier
point de limite défini est le 31 août à 11:59:59.997 (il sera expliqué plus loin pourquoi la valeur 997 est
utilisée), la combinaison de la fonction de partition et de cette contrainte permettent de garder la dernière
partition vide. Il ne s'agit que de concepts, mais il est important de savoir que le fractionnement et la fusion
sont des opérations traitées par la fonction ALTER PARTITION FUNCTION, tandis que la commutation est traitée
par la fonction ALTER TABLE.
Figure 5 - Limites de partition par spécification de plages de valeurs avant le
chargement/l'archivage des données.
Au début du mois d'octobre (dans la base de données du système transactionnel en ligne), les données du mois
de septembre doivent être déplacées vers la table partitionnée, qui est utilisée pour analyse. Le processus
d'insertion et de retrait des tables est très rapide. Le travail de préparation peut être effectué en dehors de la
table partitionnée. Ce scénario est expliqué en détail dans l'étude plus loin dans cet article, mais l'idée générale
est que vous utiliserez des « tables de transit », qui deviendront des partitions dans la table partitionnée. Une
description détaillée de ce scénario est expliqué plus loin dans l'étude de cas qui suit dans cet article. Dans ce
processus, vous retirez (figure 6) une partition d'une table pour la placer dans une table non partitionnée dans
le même groupe de fichiers. Comme la table non partitionnée existe déjà dans le même groupe de fichiers (et
que cela est important pour la réussite), SQL Server peut effectuer cette commutation sous forme de
modification des métadonnées. Dans le cas d'une modification portant uniquement sur les métadonnées, cela
peut se produire en quelques secondes contrairement à l'exécution d'une suppression qui peut prendre des
heures et entraîner un blocage sur les tables volumineuses. Une fois que cette partition est retirée, vous
conservez 13 partitions. La première partition (la plus ancienne) est désormais vide et la dernière partition (la
plus récente, qui est vide également) doit être fractionnée.
Figure 6 - Retrait d'une partition.
Pour supprimer l'ancienne partition (septembre 2003), utilisez la nouvelle option de fusion (présentée sur la
figure 7) avec la fonction ALTER TABLE. La fusion d'un point de limite supprime efficacement un point de limite,
et par conséquent une partition. Cela réduit le nombre de partitions dans lesquelles les données sont chargées
à n-1 (en l'occurrence, 12). La fusion d'une partition doit être une opération très rapide lorsque aucune ligne
n'a besoin d'être déplacée (car le point de limite fusionné ne possède pas de lignes de données). Dans ce cas,
comme la première partition est vide, aucune des lignes n'a besoin d'être déplacée de la première à la
deuxième partition. Si la première partition n'est pas vide et que le point de limite est fusionné, les lignes
doivent être déplacées de la première partition vers la deuxième partition, ce qui peut s'avérer une opération
très coûteuse. Cependant, cette situation est évitée sur le scénario de fenêtre dynamique le plus courant dans
lequel une partition vide est fusionnée avec une partition active, et qu'aucune ligne n'est déplacée.
Figure 7 - Fusion d'une partition.
Enfin, la nouvelle table doit être insérée dans la table partitionnée. Pour que cette opération soit réalisée sous
forme de modification des métadonnées, le chargement et la création des index doivent se produire dans une
nouvelle table, en dehors des limites de la table partitionnée. Pour insérer la partition, commencez par
fractionner la dernière plage de valeurs, la plus récente, qui est vide, afin de former deux partitions. Ensuite,
vous devez mettre à jour la contrainte de la table pour laisser la place à la nouvelle plage de valeurs. Là
encore, la table partitionnée contient 13 partitions. Dans le scénario de fenêtre dynamique, la dernière partition
avec une fonction de partition LEFT reste toujours vide.
Figure 8 - Fractionnement d'une partition.
Enfin, les données qui viennent d'être chargées sont prêtes à être insérées dans la douzième partition,
Septembre 2004.
Figure 9 - Insertion d'une partition.
Le résultat de la table est le suivant :
Figure 10 - Limites de partition par spécification de plages de valeurs après le
chargement/l'archivage des données.
Comme une seule partition peut être ajoutée ou supprimée à la fois, les tables dans lesquelles plusieurs
partitions doivent être ajoutées ou supprimées doivent être recréées. Pour modifier cette nouvelle structure de
partitionnement, créez tout d'abord la nouvelle table partitionnée, puis chargez les données dans celle-ci. Cette
approche présente une plus grande optimisation que celle consistant à rééquilibrer entièrement la table pour
chaque fractionnement. Ce processus est réalisé en utilisant une nouvelle fonction de partition, un nouveau
schéma de partition, puis en déplaçant les données vers la table qui vient d'être partitionnée. Pour déplacer les
données, copiez d'abord les données à l'aide des éléments INSERT newtable SELECT columnlist FROM oldtable,
puis supprimez les tables d'origine. Empêchez les utilisateurs d'apporter des modifications lorsque ce processus
est en cours afin d'éviter toute perte de données.
Pour plus d'informations, reportez-vous aux sections relatives à la modification d'une fonction de partition et à
la modification d'une table dans la documentation en ligne de SQL Server.
Procédure de création de tables partitionnées
Après vous avoir démontré le mécanisme et l'intérêt des tables partitionnées, nous vous présentons dans la
section suivante le processus d'implémentation d'une table partitionnée, ainsi que les fonctions qui participent à
ce processus. Le flux logique est le suivant :
Figure 11 - Procédure de création d'une table partitionnée ou d'un index partitionné.
Déterminer si des objets doivent être partitionnés
Si le partitionnement offre des avantages indéniables, il ne constitue pas moins une charge et une complexité
supplémentaires à l'implémentation de vos objets, ce qui peut se révéler plus un fardeau qu'un gain. Pour ce
qui vous concerne, par exemple, vous ne souhaitez peut-être pas partitionner une petite table ou une table
répondant actuellement aux exigences de performances et de maintenance. Le scénario de vente mentionné
précédemment utilise le partitionnement pour alléger la charge liée au déplacement des lignes et des données.
Demandez-vous si votre cas de figure présente ce type de charge avant de vous décider à implémenter le
partitionnement.
Déterminer la clé de partitionnement et le nombre de partitions
Si vous tentez d'améliorer les performances et le mode de gestion pour des sous-ensembles de données
volumineux et que des modèles d'accès sont définis, le partitionnement par spécification de plages de valeurs
peut alléger la contention et réduire la maintenance lorsque des données en lecture seule ne le nécessite pas.
Pour déterminer le nombre de partitions, vous devez évaluer s'il existe des regroupements et des modèles
logiques dans les données. Si vous n'utilisez généralement qu'une partie de ces sous-ensembles définis
simultanément, définissez les plages de valeurs de sorte que les requêtes soient isolées afin de n'utiliser que les
données appropriées (c'est-à-dire, uniquement la partition spécifique).
Pour plus d'informations, reportez-vous à la section traitant de la conception de tables et d'index partitionnés
dans la documentation en ligne de SQL Server.
Déterminer si plusieurs groupes de fichiers doivent être utilisés
Pour optimiser les performances et la maintenance, vous devez utiliser des groupes de fichiers pour séparer les
données. Le nombre de groupes de fichiers est déterminé partiellement par les ressources matérielles : il est
généralement approprié de disposer du même nombre de groupes de fichiers que de partitions. Ces groupes de
fichiers se trouvent souvent sur différents disques. Cependant, cela ne concerne principalement que les
systèmes dans lesquels l'analyse a tendance à être effectuée sur l'intégralité des données. Lorsque vous
disposez de plusieurs processeurs, SQL Server peut traiter plusieurs partitions en parallèle, ce qui réduit
considérablement le temps de traitement global des rapports et analyses complexes, volumineux. Dans ce cas,
vous pouvez tirer parti du traitement parallèle et de l'insertion/du retrait des partitions dans la table
partitionnée.
Créer des groupes de fichiers
Si vous souhaitez placer une table partitionnée dans plusieurs fichiers pour un meilleur équilibrage des
entrées/sorties, vous devez créer au moins un groupe de fichiers. Les groupes de fichiers peuvent contenir un
ou plusieurs fichiers. Chaque partition doit mapper vers un groupe de fichiers. Un seul groupe de fichiers peut
être utilisé pour plusieurs partitions, mais pour une meilleure gestion des données, tel que le contrôle de
sauvegarde à un niveau de granularité plus élevé, vous devez concevoir des tables partitionnées afin que seules
les données associées ou les données regroupées logiquement se trouvent dans le même groupe de fichiers. À
l'aide de la fonction ALTER DATABASE, vous pouvez ajouter un nom de groupe de fichiers logique, puis ajouter
des fichiers. Pour créer un groupe de fichier appelé 2003Q3 pour la base de données AdventureWorks,
utilisez ALTER DATABASE de la manière suivante :
ALTER DATABASE AdventureWorks ADD FILEGROUP [2003Q3]
Une fois le groupe de fichiers créé, vous utilisez ALTER DATABASE pour ajouter des fichiers au groupe de
fichiers.
ALTER DATABASE AdventureWorks
ADD FILE
(NAME = N'2003Q3',
FILENAME = N'C:\AdventureWorks\2003Q3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [2003Q3]
Vous pouvez créer une table dans des fichiers en indiquant un groupe de fichiers dans la clause ON de CREATE
TABLE. En revanche, vous ne pouvez pas créer de table dans plusieurs groupes de fichiers, sauf si elle est
partitionnée. Pour créer une table dans un seul groupe de fichiers, utilisez la clause ON de CREATE TABLE. Pour
créer une table partitionnée, vous devez d'abord disposer d'un mécanisme fonctionnel pour le partitionnement.
Les critères sur lesquels vous effectuez le partitionnement sont séparés logiquement de la table sous forme de
fonction de partition. Cette fonction de partition figure dans une définition distincte de la table. Cette séparation
physique s'avère utile car plusieurs objets peuvent utiliser la fonction de partition. Par conséquent, la première
étape du partitionnement d'une table consiste à créer la fonction de partition.
Créer la fonction de partition d'une partition par plages de valeurs
Les partitions par plages de valeurs doivent être définies avec des conditions de limite. De plus, aucune valeur,
de l'une ou l'autre extrémité de la plage de valeurs, ne peut être supprimée même si une table est restreinte
par le biais d'une contrainte CHECK. Pour permettre l'insertion périodique de données dans la table, vous avez
besoin d'une partition finale vide.
Dans une partition par plages de valeurs, définissez d'abord les points de limite : pour cinq partitions, définissez
quatre valeurs de point de limite et indiquez si les différentes valeurs correspondent à une limite supérieure de
la première partition (LEFT) ou à une limite inférieure de la seconde partition (RIGHT). En fonction de la
désignation des valeurs de gauche ou de droite, une partition est toujours vide, car elle ne possède pas de
point de limite défini explicitement.
En particulier, si la première valeur (ou condition de limite) d'une fonction de partition est « 20001001 », les
valeurs des partitions limites sont les suivantes :
Pour l'élément LEFT
la première partition correspond à toutes les données <= « 20001001 »
la deuxième partition correspond à toutes les données > « 20001001 »
Pour l'élément RIGHT
la première partition correspond à toutes les données < « 20001001 »
la deuxième partition correspond à toutes les données => « 20001001 »
Dans la mesure où les partitions par spécification de plages de valeurs sont susceptibles d'être définies en
données datetime, vous devez savoir quelle en est l'implication. Voici ce qu'implique l'utilisation de données
datetime : vous disposez toujours d'une date et d'une heure. Une date sans valeur d'heure définie implique
une heure correspondant à « 0 » pour 12:00. Si l'élément LEFT est utilisé avec ce type de données, les données
comportant la date du 1er octobre, 12:00 sont dirigées vers la première partition, tandis que le reste des
données du mois d'octobre est dirigé vers la deuxième partition. Logiquement, il est plus approprié d'utiliser les
valeurs de début avec l'élément RIGHT et des valeurs de fin avec l'élément LEFT. Ces trois clauses créent des
structures de partitionnement identiques sur le plan logique :
RANGE LEFT FOR VALUES ('20000930 23:59:59.997',
'20001231 23:59:59.997',
'20010331 23:59:59.997',
'20010630 23:59:59.997')
OU
RANGE RIGHT FOR VALUES ('20001001 00:00:00.000',
'20010101 00:00:00.000',
'20010401 00:00:00.000',
'20010701 00:00:00.000')
OU
RANGE RIGHT FOR VALUES ('20001001', '20010101', '20010401', '20010701')
Remarque L'utilisation du type de données datetime apporte une complexité supplémentaire dans cet
exemple, mais vous devez vous assurer que vous avez configuré les cas de limites adéquats. Notez la simplicité
avec l'élément RIGHT car l'heure par défaut est 12:00:00.000. Pour l'élément LEFT, la complexité ajoutée
s'explique par la précision du type de données datetime. Le fait que l'heure 23:59:59.997 DOIVE être choisie
dans les données datetime ne garantit pas la précision en millisecondes. Au lieu de cela, les données
datetime sont précises à 3,33 millisecondes. Dans le cas de l'heure 23:59:59.999, cette heure exacte n'est pas
disponible et au lieu de cela, la valeur est arrondie à l'heure la plus proche, à savoir 12:00:00.000 du jour
suivant. Avec cet arrondi, les limites ne sont pas définies correctement. Pour les données datetime, vous
devez faire preuve d'une attention extrême en ce qui concerne les valeurs utilisées exprimées en millisecondes.
Remarque Les fonctions de partition autorisent également des fonctions dans le cadre de la
définition de fonctions de partition. Vous pouvez utiliser DATEADD(ms,-3,'20010101') au lieu de
définir explicitement l'heure en utilisant « 20001231 23:59:59.997 ».
Pour plus d'informations, reportez-vous à la section traitant de la date et de l'heure du document de référence
Transact-SQL dans la documentation en ligne de SQL Server.
Pour stocker un quart des données Orders, représentant un trimestre, dans chacune des quatre partitions
actives et créer une cinquième partition pour une utilisation ultérieure (là encore, comme espace réservé pour
l'insertion ou le retrait des données dans la table partitionnée), utilisez une fonction de partition LEFT avec
quatre conditions de limite :
CREATE PARTITION FUNCTION OrderDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('20000930 23:59:59.997',
'20001231 23:59:59.997',
'20010331 23:59:59.997',
'20010630 23:59:59.997')
N'oubliez pas, quatre points de limite définis créent cinq partitions. Examinez les datasets créés par cette
fonction de partition en observant les ensembles suivants :
Point de limite « 20000930 23:59:59.997 » comme fonction LEFT (définit le schéma) :
La partition la plus gauche inclut toutes les valeurs <= « 20000930 23:59:59.997 »
Point de limite « 20001231 23:59:59.997 » :
La deuxième partition inclut toutes les valeurs > « 20000930 23:59:59.997 » et <= « 20001231 23:59:59.997
»
Point de limite « 20010331 23:59:59.997 » :
La troisième partition inclut toutes les valeurs > « 20001231 23:59:59.997 » et <= « 20010331 23:59:59.997
»
Point de limite « 20010630 23:59:59.997 » :
La quatrième partition inclut toutes les valeurs > « 20010331 23:59:59.997 » et <= « 20010630 23:59:59.997
»
Enfin, une cinquième partition inclut toutes les valeurs > « 20010630 23:59:59.997 ».
Créer le schéma de partition
Une fois que vous avez créé une fonction de partition, vous devez l'associer à un schéma de partition afin de
diriger les partitions vers des groupes de fichiers spécifiques. Lorsque vous définissez un schéma de partition,
vous devez veiller à nommer un groupe de fichiers pour chaque partition, même si plusieurs partitions se
trouvent dans le même groupe de fichiers. Pour la partition par spécification de plages de valeurs créée
précédemment (OrderDateRangePFN), il existe cinq partitions. La dernière partition, qui est vide, est créée
dans le groupe de fichiers PRIMARY. Il n'est pas nécessaire d'utiliser un emplacement spécial pour cette
partition car elle ne contiendra jamais de données.
CREATE PARTITION SCHEME OrderDatePScheme
AS
PARTITION OrderDateRangePFN
TO ([2000Q3], [2000Q4], [2001Q1], [2001Q2], [PRIMARY])
Remarque Si toutes les partitions se trouvent dans le même groupe de fichiers, il est possible d'utiliser une
syntaxe plus simple, comme celle qui suit :
CREATE PARTITION SCHEME OrderDatePScheme
AS
PARTITION OrderDateRangePFN
ALL TO ([PRIMARY])
Créer la table partitionnée
Avec la fonction de partition (structure logique) et le schéma de partition (structure physique) définis, la table
peut être créée de manière à tirer parti de ces deux structures. La table définit le schéma à utiliser et le schéma
définit la fonction. En résumé, vous devez indiquer la colonne à laquelle la fonction de partition doit s'appliquer.
Les partitions par spécification de plages de valeurs mappent toujours exactement une colonne de la table qui
doit correspondre au type de données des conditions de limite définies dans la fonction de partition. De plus, si
la table doit limiter spécifiquement le dataset (au lieu de limiter de l'infini négatif à l'infini positif), une
contrainte de vérification doit également être ajoutée.
CREATE TABLE [dbo].[OrdersRange]
(
[PurchaseOrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL ,
[RevisionNumber] [tinyint] NULL ,
[ModifiedDate] [datetime] NULL ,
[ShipMethodID] [tinyint] NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL
CONSTRAINT OrdersRangeYear
CHECK ([OrderDate] >= '20030701'
AND [OrderDate] <= '20040630 11:59:59.997'),
[TotalDue] [money] NULL
)
ON OrderDatePScheme (OrderDate)
GO
Créer les index : partitionnés ou non ?
Par défaut, les index créés dans une table partitionnée utilisent également le même schéma de partition et la
même colonne de partitionnement. Lorsque ce critère est rempli, l'index est aligné avec la table. Même si cela
n'est pas une exigence, l'alignement d'une table et de ses index permet de faciliter la gestion et
l'administration, en particulier ave le scénario de fenêtre dynamique.
Par exemple, pour créer des index uniques, la colonne de partitionnement doit être l'une des colonnes clés.
Cela permet de vérifier qu'il s'agit de la partition appropriée afin de garantir l'unicité. Par conséquent, si vous
devez partitionner une table sur une colonne et créer un index unique sur une autre colonne, la table et l'index
ne peuvent pas être alignés. Dans ce cas, l'index peut être partitionné sur la colonne unique (s'il s'agit d'une clé
unique multi-colonnes, ce peut être n'importe quelle colonne clé) ou ne pas être partitionné du tout. Gardez en
tête que cet index doit être supprimé et créé lors de l'insertion et du retrait de données dans la table
partitionnée.
Remarque Si vous envisagez de charger une table avec des données existantes et d'y ajouter
immédiatement des index, vous pouvez obtenir de meilleures performances en procédant au chargement dans
une table non partitionnée et non indexée, et en créant les index pour partitionner les données après
chargement. Lorsque vous définissez un index cluster dans le schéma de partition, cela vous permet de
partitionner efficacement la table après le chargement. C'est également un moyen approprié de partitionner
une table existante. Pour créer la même table sous forme de table non partitionnée et créer l'index cluster sous
forme d'index cluster non partitionné, remplacez la clause ON dans la table à créer par une destination de
groupe de fichiers unique. Ensuite, créez l'index cluster dans le schéma de partition une fois que les données
sont chargées.
Synthèse : études de cas
Si vous avez lu les sections relatives aux concepts, aux avantages et aux exemples de code associés au
partitionnement, vous avez acquis une certaine connaissance du processus. Cependant, pour chaque étape, un
certain nombre de paramètres et d'options sont disponibles, et dans certains cas, différents critères doivent
être remplis. Cette section résume tous ces éléments.
Partitionnement par plages de valeurs : données de vente
L'utilisation des données de vente varie. Les données du mois courant sont des données de transaction et les
données du mois précédent sont fortement sollicitées par l'analyse. L'analyse est souvent effectuée pour des
plages de données mensuelles, trimestrielles et/ou annuelles. Dans la mesure où différents analystes peuvent
avoir besoin d'étudier de grandes quantités de données variables en même temps, le partitionnement isole
mieux cette activité. Dans ce scénario, les données actives proviennent de 283 filiales et sont fournies dans
deux fichiers ASCII de format standard. Tous les fichiers sont placés sur un serveur de fichiers central, avant
3:00 le premier jour de chaque mois. Chaque fichier est classé par taille et correspond en moyenne à
86 000 ventes (Orders) par mois. Chaque commande contient une moyenne de 2,63 lignes article. Ainsi, les
fichiers OrderDetails contiennent en moyenne 226 180 lignes. Chaque mois, environ 25 millions de nouvelles
lignes Orders et 64 millions de nouvelles lignes OrderDetails sont ajoutées. Le serveur d'analyse historique
conserve les données de deux années pour analyse. Les données de deux années correspondent à un peu
moins de 600 millions de lignes Orders et un peu plus de 1,5 milliard de lignes OrderDetails. Comme
l'analyse est souvent réalisée en comparant des mois du même trimestre ou du même mois de l'année
précédente, le partitionnement par spécification de plages de valeurs est utilisé. La limite de chaque plage de
valeurs est mensuelle.
En suivant la procédure que décrit la figure 11, la table est partitionnée à l'aide du partitionnement par plages
de valeurs sur la base de la valeur OrderDate. En raison des conditions requises par ce nouveau serveur, les
analystes ont tendance à agréger et à analyser jusqu'à six mois consécutifs de données ou jusqu'à trois mois de
l'année courante et de l'année précédente (par exemple, de janvier à mars 2003 avec janvier à mars 2004).
Pour augmenter l'entrelacement des disques et isoler la plupart des regroupements de données, plusieurs
groupes de fichiers utilisent les mêmes disques physiques, mais les groupes de fichiers seront décalés de six
mois afin de réduire le blocage des disques. Les données actuelles concernent octobre 2004 et les
283 magasins gèrent leurs ventes localement. Le serveur contient des données d'octobre 2002 à la fin
septembre 2004. Pour tirer partir de la nouvelle machine multiprocesseurs 16 canaux et du réseau de stockage,
chaque mois possède son propre fichier dans un groupe de fichiers et se trouve sur un ensemble de disques
miroirs entrelacés (RAID 1+0). Pour ce qui concerne la disposition physique des données sur le disque logique
via les groupes de fichiers, le schéma ci-dessous (figure 12) décrit où se trouvent les données selon le mois
auxquelles elles correspondent.
Figure 12 - Table partitionnée Commandes.
Chacun des 12 disques logiques possède une configuration RAID 1+0. Par conséquent, le nombre total de
disques nécessaires pour les données des tables Orders et OrderDetails est de 48. Le réseau de stockage
prend en charge 78 disques et les 30 autres disques sont utilisés pour le journal de transactions, TempDB, les
bases de données système et les autres tables de moindre importance comme Customers (9 millions) et
Products (386 750 lignes), etc. Les tables Orders et OrderDetails utilisent les mêmes conditions de limite et
le même positionnement sur le disque, ainsi que le même schéma de partition. Il en résulte, en observant
uniquement les disques logiques [Disque E:\ et F:\] sur la figure 13, que les données des tables Orders et
OrderDetails figurent sur les mêmes disques pour les mêmes mois :
Figure 13 - Partition par spécification de plages de valeurs dans le positionnement d'extension sur
des matrices de disques.
Même s'il paraît complexe, ce type de partition est relativement simple à créer. La partie la plus difficile à
réaliser dans la conception d'une table partitionnée est la livraison des données provenant d'un grand nombre
de sources (283 magasins doivent posséder un mécanisme standard de livraison). Sur le serveur central, en
revanche, il suffit de créer une seule table Orders et une seule table OrderDetails. Pour créer les deux tables
sous forme de tables partitionnées, commencez par créer la fonction et le schéma de partition. Un schéma de
partition définit le positionnement physique de la partition sur le disque, donc il doit exister des groupes de
fichiers. Dans cette table, les groupes de fichiers sont nécessaires, donc l'étape suivante consiste à créer les
groupes de fichiers. La syntaxe des différents groupes de fichiers est identique à celle qui suit, mais les
24 groupes de fichiers doivent être créés. Reportez-vous au script RangeCaseStudyFilegroups.sql pour obtenir
un script complet permettant de créer les 24 groupes de fichiers.
Remarque : Vous ne pouvez pas exécuter ce script sans les lettres de lecteur appropriées. Cependant, le script
inclut une table de configuration, qui peut être modifiée pour simplifier le test. Vous pouvez modifier les
lettres/emplacements des lecteurs sur un seul disque afin de tester et découvrir la syntaxe. Veillez à ajuster la
taille des fichiers afin d'utiliser des fichiers dont la taille est exprimée en Mo plutôt qu'en Go, et envisagez de
commencer par un nombre réduit pour la taille initiale, en fonction de l'espace disque disponible.
24 fichiers et groupes de fichiers sont créés pour la base de données SalesDB. Ils suivent tous la même
syntaxe, à l'exception de l'emplacement, du nom de fichier et du nom du groupe de fichiers :
ALTER DATABASE SalesDB
ADD FILE
(NAME = N'SalesDBFG1File1',
FILENAME = N'E:\SalesDB\SalesDBFG1File1.ndf',
SIZE = 20GB,
MAXSIZE = 35GB,
FILEGROWTH = 5GB)
TO FILEGROUP [FG1]
GO
Une fois les 24 fichiers et groupes de fichiers créés, vous êtes prêt à définir la fonction et le schéma de
partition. Pour vérifier les fichiers et les groupes de fichiers, utilisez respectivement sp_helpfile et
sp_helpfilegroup.
La fonction de partition est définie dans la colonne OrderDate. Le type de données utilisé est datetime. Les
deux tables doivent stocker la valeur OrderDate pour partitionner les deux tables sur cette valeur. En effet, la
valeur de la clé de partitionnement, si les deux tables sont partitionnées avec la même valeur de clé,
aboutissent à des informations dupliquées. Cependant, cela est nécessaire dans le cadre de l'alignement et, le
plus souvent, il doit s'agir d'une colonne relativement étroite (le type de données datetime compte 8 octets).
Comme décrit dans la section « Créer la fonction de partition d'une partition par plages de valeurs » qui
précède dans cet article, la fonction est une fonction de partition par plages de valeurs, dans laquelle la
première condition de limite se trouve dans la (première) partition LEFT.
CREATE PARTITION FUNCTION TwoYearDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('20021031 23:59:59.997',
'20021130 23:59:59.997',
-- Nov 2002
'20021231 23:59:59.997',
-- Dec 2002
'20030131 23:59:59.997',
-- Jan 2003
'20030228 23:59:59.997',
-- Feb 2003
'20030331 23:59:59.997',
-- Mar 2003
'20030430 23:59:59.997',
-- Apr 2003
'20030531 23:59:59.997',
-- May 2003
'20030630 23:59:59.997',
-- Jun 2003
'20030731 23:59:59.997',
-- Jul 2003
'20030831 23:59:59.997',
-- Aug 2003
'20030930 23:59:59.997',
-- Sep 2003
'20031031 23:59:59.997',
-- Oct 2003
'20031130 23:59:59.997',
-- Nov 2003
'20031231 23:59:59.997',
-- Dec 2003
'20040131 23:59:59.997',
-- Jan 2004
'20040229 23:59:59.997',
-- Feb 2004
'20040331 23:59:59.997',
-- Mar 2004
'20040430 23:59:59.997',
-- Apr 2004
'20040531 23:59:59.997',
-- May 2004
'20040630 23:59:59.997',
-- Jun 2004
'20040731 23:59:59.997',
-- Jul 2004
'20040831 23:59:59.997',
-- Aug 2004
'20040930 23:59:59.997')
-- Sep 2004
-- Oct 2002
GO
Comme les cas de limites les plus à gauche et les plus à droite sont inclus, cette fonction de partition crée
25 partitions. La table conserve une 25ème partition qui reste vide. Aucun groupe de fichiers spécial n'est
nécessaire pour cette partition vide puisque aucune donnée ne se trouve dedans, car une contrainte restreint
les données de la table. Pour diriger les données vers les disques appropriés, un schéma de partition est utilisé
pour mapper la partition vers le groupe de fichiers. Le schéma de partition utilise un nom de groupe de fichiers
explicite pour chacun des 24 groupes de fichiers contenant des données et utilise le groupe de fichiers PRIMARY
pour la 25ème partition vide.
CREATE PARTITION SCHEME [TwoYearDateRangePScheme]
AS
PARTITION TwoYearDateRangePFN TO
( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6],
[FG7], [FG8], [FG9], [FG10],[FG11],[FG12],
[FG13],[FG14],[FG15],[FG16],[FG17],[FG18],
[FG19],[FG20],[FG21],[FG22],[FG23],[FG24],
[PRIMARY] )
GO
Il est possible de créer une table avec la même syntaxe que la prise en charge des versions précédentes en
utilisant le groupe de fichiers par défaut ou un groupe de fichiers utilisateur sous forme de table non
partitionnée ou en utilisant un schéma pour créer une table partitionnée. L'option la plus appropriée dépend du
mode de remplissage de la table et du nombre de partitions créées. Le remplissage d'un segment et la création
de l'index cluster sont susceptibles de fournir de meilleures performances que le chargement dans une table
indexée existante. De plus, lorsque vous disposez de plusieurs unités centrales, vous pouvez charger les
données dans la table grâce à des instructions parallèles BULK INSERT, puis créer les index en parallèle. Pour la
table Orders, créez la table normalement, puis chargez les données existantes par le biais d'instructions
INSERT SELECT qui extraient les données de l'exemple de base de données AdventureWorks. Pour créer la
table Orders en tant que table partitionnée, spécifiez le schéma de partition dans la clause ON de la table. La
table Orders est créée avec la syntaxe suivante :
CREATE TABLE SalesDB.[dbo].[Orders]
(
[PurchaseOrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID]
tinyint NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NULL
CONSTRAINT OrdersRangeYear
CHECK ([OrderDate] >= '20021001'
AND [OrderDate] < '20041001'),
[TotalDue] [money] NULL
) ON TwoYearDateRangePScheme(OrderDate)
GO
Dans la mesure où la table OrderDetails va utiliser elle aussi cette structure et qu'elle doit inclure la valeur
OrderDate, la table OrderDetails est créée avec la syntaxe suivante :
CREATE TABLE [dbo].[OrderDetails](
[OrderID] [int] NOT NULL,
[LineNumber] [smallint] NOT NULL,
[ProductID] [int] NULL,
[UnitPrice] [money] NULL,
[OrderQty] [smallint] NULL,
[ReceivedQty] [float] NULL,
[RejectedQty] [float] NULL,
[OrderDate] [datetime] NOT NULL
CONSTRAINT OrderDetailsRangeYearCK
CHECK ([OrderDate] >= '20021001'
AND [OrderDate] < '20041001'),
[DueDate] [datetime] NULL,
[ModifiedDate] [datetime] NOT NULL
CONSTRAINT [OrderDetailsModifiedDateDFLT]
DEFAULT (getdate()),
[LineTotal] AS (([UnitPrice]*[OrderQty])),
[StockedQty] AS (([ReceivedQty]-[RejectedQty]))
) ON TwoYearDateRangePScheme(OrderDate)
GO
L'étape suivante pour le chargement des données est traitée à l'aide de deux instructions INSERT. Ces
instructions utilisent la nouvelle base de données AdventureWorks à partir de laquelle les données sont
copiées. Installez l'exemple de base de données AdventureWorks pour copier ces données :
INSERT dbo.[Orders]
SELECT o.[PurchaseOrderID]
, o.[EmployeeID]
, o.[VendorID]
, o.[TaxAmt]
, o.[Freight]
, o.[SubTotal]
, o.[Status]
, o.[RevisionNumber]
, o.[ModifiedDate]
, o.[ShipMethodID]
, o.[ShipDate]
, o.[OrderDate]
, o.[TotalDue]
FROM AdventureWorks.Purchasing.PurchaseOrderHeader AS o
WHERE ([OrderDate] >= '20021001'
AND [OrderDate] < '20041001')
GO
INSERT dbo.[OrderDetails]
SELECT
od.PurchaseOrderID
, od.LineNumber
, od.ProductID
, od.UnitPrice
, od.OrderQty
, od.ReceivedQty
, od.RejectedQty
, o.OrderDate
, od.DueDate
, od.ModifiedDate
FROM AdventureWorks.Purchasing.PurchaseOrderDetail AS od
JOIN AdventureWorks.Purchasing.PurchaseOrderHeader AS o
ON o.PurchaseOrderID = od.PurchaseOrderID
WHERE (o.[OrderDate] >= '20021001'
AND o.[OrderDate] < '20041001')
GO
Maintenant que les données ont été chargées dans la table partitionnée, vous pouvez utiliser une nouvelle
fonction système intégrée pour déterminer la partition sur laquelle vont résider les données. La requête cidessous est utile car elle renvoie les informations suivantes, relatives à chaque partition contenant des
données : nombre de lignes dans chaque partition, valeurs OrderDate minimales et maximales. Une partition
ne contenant pas de ligne ne sera pas renvoyée par cette requête.
SELECT $partition.TwoYearDateRangePFN(o.OrderDate)
AS [Partition Number]
, min(o.OrderDate) AS [Min Order Date]
, max(o.OrderDate) AS [Max Order Date]
, count(*) AS [Rows In Partition]
FROM dbo.Orders AS o
GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate)
ORDER BY [Partition Number]
GO
SELECT $partition.TwoYearDateRangePFN(od.OrderDate)
AS [Partition Number]
, min(od.OrderDate) AS [Min Order Date]
, max(od.OrderDate) AS [Max Order Date]
, count(*) AS [Rows In Partition]
FROM dbo.OrderDetails AS od
GROUP BY $partition.TwoYearDateRangePFN(od.OrderDate)
ORDER BY [Partition Number]
GO
Enfin, une fois les tables remplies, vous pouvez créer les index cluster. Dans ce cas, l'index cluster est défini sur
la clé principale car une clé de partitionnement identifie les deux tables (pour la table OrderDetails, ajoutez
l'élément LineNumber à l'index pour garantir l'unicité). Le comportement par défaut des index créés sur des
tables partitionnées consiste à aligner l'index avec la table partitionnée dans le même schéma. Il n'est pas
nécessaire que ce dernier soit spécifié.
ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
GO
ALTER TABLE dbo.OrderDetails
ADD CONSTRAINT OrderDetailsPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
GO
La syntaxe complète qui spécifie le schéma de partition est la suivante :
ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
ON TwoYearDateRangePScheme(OrderDate)
GO
ALTER TABLE dbo.OrderDetails
ADD CONSTRAINT OrderDetailsPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
ON TwoYearDateRangePScheme(OrderDate)
GO
Jointure de tables partitionnées
Lorsque des tables alignées sont jointes, SQL Server 2005 offre la possibilité de joindre les tables en une seule
étape ou en plusieurs étapes, auquel cas les partitions individuelles sont jointes en premier, puis les sousensembles sont ajoutés. Quel que soit le mode de jointure des partitions, SQL Server évalue toujours si un
certain niveau de suppression de partition est possible ou non.
Suppression de partition
La requête ci-dessous concerne les données provenant des tables Orders et OrderDetails créées dans le
scénario précédent. La requête ne va renvoyer que les informations du troisième trimestre. En général, le
troisième trimestre inclut des mois plus lents pour ce qui est du traitement des commandes. Cependant, en
2004 ces mois ont compté parmi les mois les plus importants pour les commandes. Dans ce cas, nous nous
intéressons aux tendances associées aux produits (les quantités commandées et les dates de commande) pour
le troisième trimestre. Pour s'assurer que les tables partitionnées alignées tirent parti de la suppression de
partition lors de la jointure, vous devez spécifier la plage de partitionnement de chaque table. Dans ce cas,
comme la clé principale de la table Orders est une clé composite utilisant les valeurs OrderDate et OrderID,
la jointure entre ces tables indique que la valeur OrderDate doit être identique entre les tables. L'argument de
recherche (SARG) est appliqué aux deux tables partitionnées. La requête permettant d'extraire ces données est
la suivante :
SELECT o.OrderID, o.OrderDate, o.VendorID, od.ProductID, od.OrderQty
FROM dbo.Orders AS o
INNER JOIN dbo.OrderDetails AS od
ON o.OrderID = od.OrderID
AND o.OrderDate = od.OrderDate
WHERE o.OrderDate >= '20040701'
AND o.OrderDate <= '20040930 11:59:59.997'
GO
Comme indiqué sur la figure 14, certains éléments de clé doivent être recherchés lors de l'examen de la sortie
du plan de requêtes réel ou estimé : tout d'abord (à l'aide de SQL Server Management Studio), lorsque vous
passez le curseur sur les tables en cours d'accès, vous découvrez « Nombre d'exécutions estimé » ou « Nombre
d'exécutions ». Dans ce cas, un trimestre, ou trois mois, de données sont affichés. Chaque mois possède sa
propre partition et il y a trois exécutions pour la consultation de ces données : une pour chaque table.
Figure 14 - Nombre d'exécutions.
Comme indiqué sur la figure 15, SQL Server supprime toutes les partitions inutiles et ne choisit que les
partitions contenant les données appropriées. Examinez PARTITION ID:([PtnIds1017]) dans la section
Argument pour découvrir ce qui est évalué. Vous vous demandez peut-être d'où vient l'expression
« PtnIds1017 ». Il s'agit d'une représentation logique des partitions auxquelles cette requête accède. Si vous
passez le curseur sur Constant Scan dans la partie supérieure du plan de requêtes, vous découvrez que
l'argument VALUES(((21)), ((22)), ((23))) s'affiche. Cet argument représente les numéros de partition.
Figure 15 - Suppression de partition.
Pour vérifier les données existant sur chacune des partitions, et uniquement ces partitions, utilisez une version
légèrement modifiée de la requête utilisée précédemment pour accéder aux nouvelles fonctions système
intégrées pour les partitions :
SELECT $partition.TwoYearDateRangePFN(o.OrderDate)
AS [Partition Number]
, min(o.OrderDate) AS [Min Order Date]
, max(o.OrderDate) AS [Max Order Date]
, count(*) AS [Rows In Partition]
FROM dbo.Orders AS o
WHERE $partition.TwoYearDateRangePFN(o.OrderDate) IN (21, 22, 23)
GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate)
ORDER BY [Partition Number]
GO
À ce stade, vous pouvez reconnaître graphiquement la suppression de partition. Une technique d'optimisation
supplémentaire peut être utilisée pour les tables et les index partitionnés, en particulier s'ils sont alignés avec
les tables avec lesquelles la jointure est réalisée. SQL Server peut effectuer plusieurs jointures en commençant
par joindre chacune des partitions.
Pré-jointure des tables alignées
Dans la même requête, SQL Server ne se contente pas de supprimer des partitions. SQL Server exécute
également les jointures entre les partitions restantes individuellement. Outre la consultation du nombre
d'exécutions pour chaque accès aux tables, notez les informations associées à la jointure de fusion. Si vous
passez le curseur sur la jointure de fusion, vous pouvez constater que la jointure de fusion a été exécutée trois
fois.
Figure 16 - Jointure de tables partitionnées.
Sur la figure 16, notez qu'une autre jointure de boucle imbriquée est exécutée. Cette opération semble s'être
réalisée après la jointure de fusion, alors qu'en réalité les ID de partition ont déjà été transmis dans chaque
recherche ou analyse effectuée dans les tables. Cette jointure finale ne fait que regrouper les deux ensembles
de données segmentées, pour garantir que chacune corresponde bien à l'ID de partition défini au début (dans
l'expression d'analyse de constante).
Scénario de fenêtre dynamique
Lorsque les données du mois suivant sont disponibles, octobre 2004 en l'occurrence, il existe une séquence
d'étapes spécifique à suivre pour utiliser des groupes de fichiers existants et pour insérer/retirer des données.
Dans ce scénario de ventes, les données actuellement dans FG1 sont les données correspondant au mois
d'octobre 2002. Maintenant que les données du mois d'octobre 2004 sont disponibles, vous avez deux
possibilités selon l'espace disponible et les critères d'archivage. N'oubliez pas, pour insérer ou retirer
rapidement une partition dans une table, la commutation ne doit modifier que les métadonnées. En particulier,
la nouvelle table (la source ou la destination, c'est-à-dire une partition déguisée) doit être créée dans le même
groupe de fichiers que celui dans lequel l'insertion/le retrait a lieu. Si vous envisagez de continuer à utiliser les
mêmes groupes de fichiers (FG1 en l'occurrence), vous devez déterminer comment prendre en compte les
critères d'espace et d'archivage. Pour réduire la quantité de temps pendant laquelle la table ne possède pas
deux années complètes de données et si vous avez de l'espace, vous pouvez charger les données actuelles
(octobre 2004) dans FG1 sans supprimer les données à archiver (octobre 2002). Cependant, si vous ne
disposez pas de suffisamment d'espace pour conserver le mois courant et le mois à archiver, vous devez
d'abord retirer l'ancienne partition (et la supprimer).
Quoi qu'il en soit, l'archivage doit être facile et il est probablement déjà résolu. Une bonne méthode pour
l'archivage consiste à sauvegarder immédiatement le groupe de fichiers une fois que la nouvelle partition est
chargée et insérée, et non pas juste avant que vous n'ayez l'intention de la retirer. Par exemple, en cas de
défaillance de la matrice RAID, le groupe de fichiers peut être restauré au lieu d'avoir à recréer ou à recharger
les données. Dans ce cas en particulier, comme la base de données n'a été partitionnée que récemment, vous
pouvez avoir effectué une sauvegarde complète une fois que la structure de partitionnement a été stabilisée. La
sauvegarde totale de la base de données n'est pas la seule possibilité. Il existe de nombreuses stratégies de
sauvegarde qui peuvent être implémentées dans SQL Server 2005. Un grand nombre d'entre elles offrent de
meilleurs niveaux de granularité pour la sauvegarde et la restauration. Comme la majorité des données ne
varie pas, vous pouvez sauvegarder tous les groupes de fichiers individuellement une fois qu'ils sont chargés.
En fait, cette procédure doit devenir partie intégrante de votre stratégie de partitionnement tournante. Pour
plus d'informations, reportez-vous à la section traitant des sauvegardes de fichier et de groupe de fichiers du
document d'administration de SQL Server de la documentation en ligne de SQL Server.
Maintenant que la stratégie est en place, vous devez comprendre le processus et la syntaxe exacts. La syntaxe
et le nombre d'étapes paraissent complexes. Cependant, le processus est identique pour chaque mois. En
utilisant l'exécution SQL dynamique, vous pouvez facilement automatiser ce processus en suivant cette
procédure :

Gestion de la table de transit pour la partition qui va être insérée.

Gestion de la deuxième table de transit pour la partition qui va être retirée.

Transfert des anciennes données et rappel des nouvelles données dans la table partitionnée.

Suppression des tables de transit.

Sauvegarde du groupe de fichiers.
La syntaxe et les recommandations pour chaque étape sont détaillées dans les sections qui suivent, tout
comme les notes permettant d'automatiser ce processus par le biais de l'exécution SQL dynamique.
Gestion de la table de transit pour la partition qui va être insérée
1.
Créez la table de transit, qui est une future partition déguisée. Cette table de transit doit posséder une
contrainte qui restreint ses données uniquement aux données valides pour la partition à créer. Pour de
meilleures performances, chargez les données dans un segment non indexé sans contrainte, puis
ajoutez la contrainte (reportez-vous à l'étape 3) WITH CHECK avant d'insérer la table dans la table
partitionnée.
2.
CREATE TABLE SalesDB.[dbo].[OrdersOctober2004]
3.
(
4.
[OrderID] [int] NOT NULL,
5.
[EmployeeID] [int] NULL,
6.
[VendorID] [int] NULL,
7.
[TaxAmt] [money] NULL,
8.
[Freight] [money] NULL,
9.
[SubTotal] [money] NULL,
10.
[Status] [tinyint] NOT NULL,
11.
[RevisionNumber] [tinyint] NULL,
12.
[ModifiedDate] [datetime] NULL,
13.
[ShipMethodID] [tinyint] NULL,
14.
[ShipDate] [datetime] NOT NULL,
15.
[OrderDate] [datetime] NOT NULL,
16.
[TotalDue] [money] NULL
17.
) ON [FG1]
GO
Pour l'automatisation : Cette table est facile à créer car il s'agit toujours du mois courant. En fonction
du moment où le processus est exécuté, la détection du mois est facile avec les fonctions intégrées comme
DATENAME(m, getdate()). Comme la structure de la table doit correspondre à la table existante, la
modification principale de chaque mois est le nom de la table. Cependant, vous pouvez utiliser le même
nom tous les mois, car la table n'a pas besoin d'exister une fois qu'elle a été ajoutée à la partition. Elle
existe toujours une fois que les données ont été insérées dans la table partitionnée, mais vous pouvez
supprimer la table de transit une fois que la commutation a été effectuée. La plage de dates doit elle aussi
être modifiée. Dans la mesure où vous utilisez des données datetime et qu'il n'y a pas de problème
d'arrondi concernant le mode de stockage de l'heure, vous devez pouvoir déterminer par le biais du code
la valeur appropriée exprimée en millisecondes. La manière la plus simple de trouver la dernière valeur
datetime pour la fin du mois est de prendre le mois que vous utilisez, d'y ajouter 1 mois et d'y soustraire
2 ou 3 millisecondes. Vous ne pouvez pas soustraire seulement 1 milliseconde car 59 999 est arrondi à
0 000, qui correspond au premier jour du mois suivant. Vous pouvez soustraire 2 ou 3 millisecondes car
2 millisecondes est arrondi à 0,997 et 3 millisecondes est égal à 0,997. 0,997 est une valeur valide qui
peut être stockée. Cela vous donne la valeur de fin correcte pour la plage datetime :
DECLARE @Month
@Year
nchar(2),
nchar(4),
@StagingDateRange
nchar(10)
SELECT @Month = N'11', @Year = N'2004'
SELECT @StagingDateRange = @Year + @Month + N'01'
SELECT dateadd(ms, -2, @StagingDateRange)
La table est recréée tous les mois car elle doit se trouver dans le groupe de fichiers dans lequel les
données sont insérées et retirées. Pour déterminer le groupe de fichiers approprié à prendre en compte,
utilisez la requête de table système ci-dessous combinée à la fonction $partition présentée
précédemment. Spécifiez une date dans la plage de valeurs affichée. Il s'agit de la partition et du groupe
de fichiers dans lesquels l'ensemble du travail est réalisé. Les sections soulignées doivent être modifiées
pour votre table, la fonction de partition et la date spécifique.
SELECT ps.name AS PSName,
dds.destination_id AS PartitionNumber,
fg.name AS FileGroupName
FROM (((sys.tables AS t
INNER JOIN sys.indexes AS i
ON (t.object_id = i.object_id))
INNER JOIN sys.partition_schemes AS ps
ON (i.data_space_id = ps.data_space_id))
INNER JOIN sys.destination_data_spaces AS dds
ON (ps.data_space_id = dds.partition_scheme_id))
INNER JOIN sys.filegroups AS fg
ON dds.data_space_id = fg.data_space_id
WHERE (t.name = 'Orders') AND (i.index_id IN (0,1)) AND
dds.destination_id = $partition.TwoYearDateRangePFN('20021001')
18. Chargez la table de transit avec les données. Si les fichiers sont cohérents, ce processus doit être
traité par le biais d'instructions BULK INSERT.
Pour l'automatisation : Ce processus est le plus complexe à automatiser. Vous devez vous assurer que
tous les fichiers ont été chargés et même songer à les charger en parallèle. Une table qui conserve la trace
des fichiers chargés et de l'emplacement des fichiers permet de contrôler ce processus. Vous pouvez créer
une tâche SQL Agent qui vérifie les fichiers toutes les quelques minutes, prélève les nouveaux fichiers,
puis exécute plusieurs instructions d'insertion en bloc.
19. Une fois les données chargées, vous pouvez ajouter la contrainte. Pour que les données soient
approuvées, la contrainte doit être ajoutée avec le paramètre WITH CHECK. Le paramètre WITH
CHECK est la valeur par défaut. Ainsi, il n'est pas nécessaire de le spécifier mais il est important de ne
pas utiliser le paramètre WITH NOCHECK.
20. Indexez la table de transit. Elle doit posséder le même index cluster que la table dans laquelle elle va
devenir une partition.
21.
ALTER TABLE [OrdersOctober2004]
22.
ADD CONSTRAINT OrdersOctober2004PK
23.
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
24.
ON [FG1]
GO
Pour l'automatisation : cette étape est simple. À l'aide des informations de mois et de groupe de fichiers
de l'étape 1, vous pouvez créer cet index cluster.
ALTER TABLE SalesDB.[dbo].[OrdersOctober2004]
WITH CHECK
ADD CONSTRAINT OrdersRangeYearCK
CHECK ([OrderDate] >= '20041001'
AND [OrderDate] <= '20041031 23:59:59.997')
GO
Gestion de la deuxième table de transit pour la partition retirée
3.
1.
Créez une deuxième table de transit. Il s'agit d'une table vide contenant les données de la partition
lorsque celles-ci sont retirées.
2.
CREATE TABLE SalesDB.[dbo].[OrdersOctober2002]
(
4.
[OrderID] [int] NOT NULL,
5.
[EmployeeID] [int] NULL,
6.
[VendorID] [int] NULL,
7.
[TaxAmt] [money] NULL,
8.
[Freight] [money] NULL,
9.
[SubTotal] [money] NULL,
10.
[Status] [tinyint] NOT NULL,
11.
[RevisionNumber] [tinyint] NULL,
12.
[ModifiedDate] [datetime] NULL,
13.
[ShipMethodID] [tinyint] NULL,
14.
[ShipDate] [datetime] NOT NULL,
15.
[OrderDate] [datetime] NOT NULL,
16.
[TotalDue] [money] NULL
17.
) ON [FG1]
GO
18. Indexez la table de transit. Elle doit posséder le même index cluster que la table dans laquelle elle va
devenir une partition (et la partition va devenir cette table).
19.
ALTER TABLE [OrdersOctober2002]
20.
ADD CONSTRAINT OrdersOctober2002PK
21.
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
22.
ON [FG1]
GO
Transfert des anciennes données et rappel des nouvelles données dans la table partitionnée
1.
Retirez les anciennes données, pour les faire passer dans la seconde table de transit.
2.
ALTER TABLE Orders
3.
SWITCH PARTITION 1
4.
TO OrdersOctober2002
GO
5.
Modifiez la fonction de partition de manière à supprimer le point de limite du mois d'octobre 2002.
6.
ALTER PARTITION FUNCTION TwoYearDateRangePFN()
7.
MERGE RANGE ('20021031 23:59:59.997')
GO
8.
Cette opération supprime également l'association entre le groupe de fichiers et le schéma de partition.
En particulier, FG1 ne fait plus partie du schéma de partition. Puisque vous allez rappeler les nouvelles
données par le biais des 24 mêmes partitions existantes, vous devez définir FG1 comme la « partition
suivante utilisée » qui sera la prochaine partition à utiliser pour un fractionnement.
9.
ALTER PARTITION SCHEME TwoYearDateRangePScheme
10.
NEXT USED [FG1]
GO
11. Modifiez la fonction de partition de manière à ajouter le nouveau point de limite du mois
d'octobre 2004.
12.
ALTER PARTITION FUNCTION TwoYearDateRangePFN()
13.
SPLIT RANGE ('20041031 23:59:59.997')
GO
14. Modifiez la définition de la contrainte dans la table de base, le cas échéant, afin d'accueillir la nouvelle
plage de données. Dans la mesure où l'ajout de contraintes peut s'avérer coûteux (pour vérifier les
données), il est recommandé de continuer à étendre la date au lieu de supprimer et de recréer la
contrainte. Pour le moment, il n'existe qu'une seule contrainte (OrdersRangeYearCK) mais pour les
dates futures, il y aura deux contraintes.
15. ALTER TABLE Orders
16.
ADD CONSTRAINT OrdersRangeMaxOctober2004
17.
CHECK ([OrderDate] < '20041101')
18.
GO
19.
ALTER TABLE Orders
20.
ADD CONSTRAINT OrdersRangeMinNovember2002
21.
CHECK ([OrderDate] >= '20021101')
22.
GO
23.
ALTER TABLE Orders
24.
DROP CONSTRAINT OrdersRangeYearCK
GO
25. Insérez les nouvelles données à partir de la première table de transit.
26.
ALTER TABLE OrdersOctober2004
27.
SWITCH TO Orders PARTITION 24
GO
Suppression des tables de transit
Étant donné que toutes les données seront archivées à l'étape suivante, la dernière de la procédure, les
données de transit sont inutiles. Le moyen le plus rapide pour supprimer ces tables consiste à utiliser
l'instruction DROP TABLE.
DROP TABLE dbo.OrdersOctober2002
GO
DROP TABLE dbo.OrdersOctober2004
GO
Sauvegarde du groupe de fichiers
Ce que vous décidez de sauvegarder à la dernière étape est fonction de votre stratégie de sauvegarde. Si une
stratégie de sauvegarde de fichier ou de groupe de fichiers a été choisie, vous devez effectuer la sauvegarde
d'un fichier ou d'un groupe de fichiers. Si une stratégie de sauvegarde basée sur une base de données complète
a été choisie, vous pouvez effectuer une sauvegarde intégrale ou une sauvegarde différentielle de la base de
données.
BACKUP DATABASE SalesDB
FILEGROUP = 'FG1'
TO DISK = 'C:\SalesDB\SalesDB.bak'
GO
Partitionnement par spécification de listes : données régionales
Si votre table contient des données provenant de plusieurs régions et que des analyses sont souvent effectuées
sur une seule région ou si vous recevez périodiquement des données pour chaque région, envisagez d'utiliser
des partitions par spécification de plages de valeurs définies sous forme de liste. En d'autres termes, vous
utiliserez une fonction qui définit explicitement chaque partition comme valeur pour une région. Par exemple,
imaginez une société espagnole ayant des clients en Espagne, en France, en Allemagne, en Italie et au
Royaume-Uni. Ses données de vente sont toujours analysées par pays. Pour sa table, cinq partitions peuvent
être utilisées, soit une par pays.
La création de cette partition par spécification de liste est presque identique à la partition par spécification de
plages de valeurs pour les dates, à l'exception du fait que les limites de la plage de valeur ne possèdent pas
d'autres valeurs en dehors de la clé de partition effective. Au lieu de cela, il s'agit d'une liste et non de plages
de valeurs. Même si c'est une liste, les conditions de limite doivent inclure les valeurs les plus à gauche et les
valeurs les plus à droite. Pour créer cinq partitions, spécifiez-en seulement quatre dans la fonction de partition.
Les valeurs n'ont pas besoin d'être ordonnées (SQL Server les trie en interne), mais la manière la plus logique
d'obtenir le nombre correct de partitions consiste à trier les valeurs de partition et à écarter la valeur la plus
élevée de la dernière partition (lorsque la fonction de partition LEFT est utilisée) ou de trier les valeurs de
partition et de commencer par la seconde valeur la plus faible (lorsque la fonction de partition RIGHT est
utilisée).
Comme il y a cinq partitions, vous devez disposer de cinq groupes de fichiers. Dans ce cas, les groupes de
fichiers sont nommés selon les données en cours de stockage. Le fichier de script,
RegionalRangeCaseStudyFilegroups.sql, est un script qui affiche l'intégralité de cette syntaxe. Chaque groupe
de fichiers est créé à l'aide des mêmes paramètres. Cependant, cela n'a pas lieu d'être si les données ne sont
pas équilibrées. Seul le groupe de fichiers et le fichier correspondant à l'Espagne sont affichés. Chacun des
quatre autres groupes de fichiers et fichiers possède les mêmes paramètres, mais se trouve sur différents
disques et possède le nom spécifique de la partition correspondant au pays.
ALTER DATABASE SalesDB
ADD FILEGROUP [Spain]
GO
ALTER DATABASE SalesDB
ADD FILE
(NAME = N'SalesDBSpain',
FILENAME = N'C:\SalesDB\SalesDBSpain.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [Spain]
GO
L'étape suivante consiste à créer la fonction qui spécifie uniquement quatre partitions à l'aide de la fonction
LEFT pour la condition de limite. Dans ce cas, la liste inclut tous les pays à l'exception du Royaume-Uni qui
arrive en dernier dans la liste alphabétique.
CREATE PARTITION FUNCTION CustomersCountryPFN(char(7))
AS
RANGE LEFT FOR VALUES ('France', 'Germany', 'Italy', 'Spain')
GO
Pour placer les données dans le groupe de fichiers pour lequel elles ont été nommées, le schéma de partition
est répertorié dans l'ordre alphabétique. Les cinq groupes de fichiers doivent tous être spécifiés dans la syntaxe
du schéma de partition.
CREATE PARTITION SCHEME [CustomersCountryPScheme]
AS
PARTITION CustomersCountryPFN
TO ([France], [Germany], [Italy], [Spain], [UK])
GO
Enfin, la table Customers peut être créée dans le nouveau schéma CustomersCountryPScheme.
CREATE TABLE [dbo].[Customers](
[CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
[ContactTitle] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [char](7) NOT NULL,
[Phone] [nvarchar](24) NULL,
[Fax] [nvarchar](24) NULL
) ON CustomersCountryPScheme (Country)
GO
Tandis que les partitions par spécification de plages de valeurs sont définies comme des plages de prise en
charge uniquement elles offrent également un moyen d'effectuer d'autres types de partitions, comme des
partitions par spécification de liste.
Résumé
SQL Server 2005 offre un moyen de gérer facilement et avec cohérence des tables et des index volumineux
grâce au partitionnement, qui permet de gérer des sous-ensembles de vos données en dehors de la table
active. Cela procure une gestion simplifiée, des performances accrues et une logique d'application abstraite, car
le schéma de partition est totalement transparent pour l'application. Lorsque les données sont associées à des
regroupements logiques (plages de valeurs ou liste), et que des requêtes plus importantes doivent analyser les
données dans ces plages de valeurs prédéfinies et homogènes, et gérer les données entrantes et sortantes
dans ces mêmes plages de valeurs prédéfinies, le partitionnement par spécification de plages de valeurs
constitue une solution simple. Si vous observez l'analyse sur des grandes quantité de données sans plage de
valeurs spécifique à utiliser ou si toutes les requêtes accèdent à la plupart des données, voire à toutes, le
recours à plusieurs groupes de fichiers sans techniques de positionnement spécifique est une solution plus
simple qui permettra tout de même des gains en terme de performances.
Scripts utilisés dans cet article
Les scripts utilisés dans les exemples de code pour cet article sont disponibles dans le fichier
SQLServer2005PartitionedTables.zip. Voici une description de chacun des fichiers contenus dans le fichier ZIP.
RangeCaseStudyScript1-Filegroups.sql : inclut la syntaxe servant à créer les groupes de fichiers et les
fichiers nécessaires à l'étude de cas de table partitionnée par spécification de plages de valeurs. Ce script
permet des modifications servant à créer cet exemple sur un ensemble réduit de disques comportant des
fichiers de plus petite taille (en Mo plutôt qu'en Go). Il contient également le code permettant d'importer des
données par le biais d'instructions INSERT...SELECT afin que vous puissiez évaluer où sont positionnées les
données dans les fonctions de partition appropriées.
RangeCaseStudyScript2-PartitionedTable.sql : inclut la syntaxe servant à créer la fonction et le schéma de
partition, ainsi que les tables partitionnées associées à l'étude de cas de la table partitionnée par spécification
de plages de valeurs. Ce script inclut également les contraintes et les index appropriés.
RangeCaseStudyScript3-JoiningAlignedTables.sql : inclut les requêtes qui présentent les différentes
stratégies de jointure offertes par SQL Server pour les tables partitionnées.
RangeCaseStudyScript4-SlidingWindow.sql : inclut la syntaxe et les processus associés à la gestion
mensuelle de l'étude de cas de table partitionnée par spécification de plages de valeurs. Dans ce script, vous
insérez et retirez des données dans la table Orders en les faisant glisser. Vous avez également la possibilité de
votre propre chef, de créer le même processus afin d'insérer et de retirer des données de la table OrderDetails
en les déplaçant. Conseil : Reportez-vous à l'instruction Insert dans le script RangeCaseStudyScript2 pour la
table et corrigez les colonnes de données à insérer pour OrderDetails.
RegionalRangeCaseStudyFilegroups.sql : inclut la syntaxe servant à créer les groupes de fichiers et les
fichiers nécessaires à l'étude de cas de table partitionnée par spécification de régions. En fait, il s'agit d'une
partition par spécification de plages de valeurs servant à simuler un schéma de partition par spécification de
liste.
RegionalRangeCaseStudyPartitionedTable.sql : inclut la syntaxe servant à créer la fonction et le schéma
de partition, ainsi que les tables partitionnées par spécification de régions associées à l'étude de cas de la table
partitionnée par spécification de plages de valeurs.