Définition des partitions SQL Server

Quelle définition pour les partitions SQL Server ?

Dans une base de données SQL Server, les partitions permettent de diviser physiquement les données d’une même table sur le ou les disques. Cette division des données permet d’accéder directement à une partition unique au lieu de toutes les données de la table.

Tout d’abord, pourquoi toutes les grandes tables devraient-elles avoir des partitions ? Lorsque l’on accède à des centaines de millions de lignes, il devient très difficile d’obtenir un grand nombre de données en quelques secondes. Les partitions permettent d’améliorer les performances des requêtes.

Table des matières

Les avantages des partitions SQL Server

Le principal avantage d’une table partitionnée est évidemment d’améliorer les performances des requêtes. Il évite les balayages complets en ciblant uniquement les partitions nécessaires.
Les partitions de table permettent de transférer seulement un sous-ensemble de données basé sur la clé fonctionnelle au lieu de l’ensemble des données. Prenons l’exemple de données mensuelles. La requête transfère uniquement le dernier mois stocké dans une seule partition. C’est beaucoup plus rapide que de sélectionner l’ensemble des données, y compris l’année complète et les années précédentes.
Les partitions sur les tables permettent une maintenance beaucoup plus flexible comme la réorganisation ou la reconstruction des index.

Par exemple, considérons un projet construit avec Microsoft Business Intelligence avec un magasin de données opérationnel, un entrepôt de données, de magasins de données analytiques (Data Marts) et un modèle tabulaire SSAS. Quotidiennement, les dernières données mises à jour pourraient n’être que celles du mois en cours. Il est donc plus intéressant de ne charger que le mois en cours. Il permet également des suppressions et des insertions rapides pour rafraîchir un ensemble de données.

Veuiller noter que toutes les versions disponibles avant et jusqu’à SQL Server 2012 ne supportent que jusqu’à 1000 partitions par table.

Définitions des éléments composants les partitions MS SQL

D’autre part, le partitionnement utilise différents objets SQL obligatoires : les fonctions de partition, les schémas de partition et les colonnes de partitionnement.

Une colonne de partitionnement sépare les données dans les différentes partitions. Il peut s’agir d’une année, d’un mois ou de tout autre type de données. Le choix de la colonne est basé sur l’utilisation réelle des données et sur la colonne la plus utilisée pour interroger les données.
Une fonction de partition définit la manière de stocker et de mapper les données sur une partition, c’est une fonction du serveur SQL. C’est-à-dire que sur la base d’une colonne, la ligne complète sera dirigée vers la partition 1 ou 2, etc.
Un schéma de partitionnement fait correspondre les partitions de données à un ensemble de fichiers physiques.

Par exemple, considérons une fonction de partition basée sur le numéro de l’année. La première colonne pourrait être celle des années historiques, c’est-à-dire avant 2018. Puis les données avant 2019 et l’année 2020, puis seulement 2020 et enfin les données associées après 2020. Ainsi, l’exemple de partition améliore les performances des requêtes.

Un exemple pratique d’une fonction et d’un schéma de partition

Un article complet pour créer une table partitionnée par année, avec une fonction de partition, les groupes de fichiers, et enfin le schéma de partitionnement. Toutes les requêtes sont disponibles dans cet article.

Créer une table SQL Server avec des partitions

Créer une table SQL Server avec des partitions

Pour conclure, le premier article de la série expose la définition des partitions SQL Server et définit ce qu’est une table partitionnée. Consulter cette série sur la gestion des index et comment créer un index SQL Server.

Consulter cette foire aux questions sur les partitions de table SQL Server.

Qu’est-ce qu’une table partitionnée sous SQL Server ?

Le partitionnement divise techniquement une table ou un index en différentes parties basées sur les valeurs des colonnes. Les partitions sont physiquement indépendantes pour améliorer les performances et faciliter la maintenance, les chargements et les transferts de données. Une partition utilise une fonction de partition, un schéma de partition et une colonne partitionnée.

Comment partitionner une table SQL ?

Pour partitionner une table SQL, choisir la colonne principale utilisée dans les requêtes, les chargements ou les rapports. Créer une fonction de partition, par exemple les mois passés, le mois précédent, le mois en cours et les mois futurs. Créer les groupes de fichiers associés qui seront utilisés par le schéma de partition.

Quelle est la différence entre un index et une partition ?

Un index structure les données à l’intérieur d’une table logique. Ensuite, les administrateurs de la base de données (DBA) reconstruisent et réorganisent les index. Une table partitionnée permet de diviser physiquement les données et d’y accéder indépendamment. Un index peut lui-même être partitionné pour de meilleures performances.

Comment maintenir automatiquement une partition ?

Une bonne pratique est de maintenir automatiquement la fonction de partition. Ainsi, elle crée les nouvelles partitions au fur et à mesure de l’évolution des données dans le temps. Par exemple, utiliser la date courante de la base de données pour ajouter chaque nouveau mois à une partition.