Créer une table SQL Server avec des partitions

Comment créer une table avec des partition pour dans SQL Server ? Considérons une grande table stockée dans une base SQL Serveur contenant des données de ventes. Cette table contient une colonne qui stocke l’année de la vente et la table stocke des millions de lignes.

Considérons également que certains rapports lisent ces données pour afficher les totaux annuels et les comparent à l’année précédente.

Scripts pour créer une table SQL Server avec des partitions étape par étape

Pour commencer, utilisons cette table de ventes comme exemple pour la création de la partition.

CREATE TABLE [dbo].[SALES]
(
     [Year] INT,
     [MonthName] NVARCHAR(50), -- alphanumeric
     [MonthCurrent] BIT, -- Boolean, 0 or 1 , false / true
     [NumberMonth] TINYINT, -- very small integer, from 0 to 255
     [EmployeeNumber] SMALLINT, -- small integer, minimum -2^15, maximum 2^15
     [NumberOfClients] INTEGER, -- integer, minimum -2^31, maximum 2^31
     [NumberOfSales] BIGINT, -- big integer, minimum: -2^63, maximum 2^63
     [Amount_ET] NUMERIC(15,5), -- numeric, 15 digits, with 5 after the comma
     [Amount_IT] DECIMAL(15,5) -- decimal, 15 digits, with 5 after the comma
);
GO

Pour clarifier, le partitionnement d’une table se fait avec quatre objets principaux et quatre décisions à prendre. La décision principale est de choisir la colonne de partitionnement.

  1. La première étape consiste à choisir la colonne de partitionnement
  2. La deuxième étape consiste à créer une fonction de partitionnement
  3. La troisième étape consiste à créer les groupes de fichiers nécessaires
  4. La quatrième étape consiste à créer un schéma de partitionnement
  5. Dernière étape : mettre à jour la table existante pour utiliser le schéma de partitionnement.

La première étape consiste à choisir la colonne de partitionnement

Tout d’abord, il faut choisir la meilleure colonne pour la partition. Comme l’article l’expose plus haut dans l’introduction, de nombreuses requêtes utilisent l’année comme filtre pour afficher les chiffres annuels et les comparaisons d’une année sur l’autre.

Le choix d’une partition sur la colonne année est intéressant. La colonne de partitionnement est la colonne Année et c’est un entier.

Les différentes partitions sur l’année vont répartir physiquement les données du disque sur plusieurs groupes de fichiers. Cette opération est également appelée partitionnement d’une table existante. Vous trouverez plus de détails dans cet article sur la définition d’une partition SQL Server.

De plus, la table reste un objet logique unique de SQLServeur, mais physiquement elle est divisée en différents fichiers pour isoler et améliorer l’accès à chaque partition.

La deuxième étape consiste à créer une fonction de partition SQL Server

Ensuite, pour créer une fonction de partition, il suffit d’utiliser une instruction CREATE afin de créer la fonction de partition. Les paramètres sont le type de données de la colonne de partitionnement, le type de partition qui peut être droite ou gauche, et la plage de valeurs.

Par exemple, cet exemple de code T-SQL montre comment créer une fonction de partition basée sur les années précédentes, la dernière année, l’année en cours et les années suivantes.

CREATE PARTITION FUNCTION ufn_Partition_Sales (int)
AS RANGE RIGHT
FOR VALUES (
     2018,
     2019,
     2020
);
Script T-SQL pour créer une fonction de partition avec l'option range right comme schéma de partition
Script T-SQL pour créer une fonction de partition avec l’option range right comme schéma de partition

La troisième étape est de créer les groupes de fichiers nécessaires pour les partitions

De plus, un schéma de partition utilise une fonction de partition et un schéma de partition utilise des groupes de fichiers. Afin de créer le schéma de partition, créez d’abord les groupes de fichiers à utiliser.

Avant tout, assurez-vous d’ajuster le code avant de l’exécuter. Utilisez le code ci-dessous pour créer quatre groupes de fichiers supplémentaires à la base de données Expert-Only existante. Ensuite, l’exemple de code suivant crée les groupes de fichiers dans le système de fichiers. En outre, consultez la documentation officielle sur les tables et les index partitionnés.

USE [Expert-Only];  
GO  

ALTER DATABASE [Expert-Only] ADD FILEGROUP Sales1filegroup;  
GO  

ALTER DATABASE [Expert-Only] ADD FILEGROUP Sales2filegroup;  
GO  

ALTER DATABASE [Expert-Only] ADD FILEGROUP Sales3filegroup;  
GO  

ALTER DATABASE [Expert-Only] ADD FILEGROUP Sales4filegroup;  
GO
Modifier la base de données pour ajouter des groupes de données logiques
Modifier la base de données pour ajouter des groupes de données logiques

En outre, l’étape suivante est obligatoire et attribue les fichiers physiques aux groupes de fichiers logiques existants. Pour ce faire, ajoutons un fichier de données secondaire à chaque groupe de fichiers créé.

ALTER DATABASE [Expert-Only] ADD FILE   
(  
    NAME = Sales1datafile,  
    FILENAME = 
     'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Sales1df.ndf',
    SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 10MB  
)  
TO FILEGROUP Sales1filegroup;
GO

ALTER DATABASE [Expert-Only] ADD FILE   
(  
    NAME = Sales2datafile,  
    FILENAME = 
     'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Sales2df.ndf',
    SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 10MB  
)  
TO FILEGROUP Sales2filegroup;
GO 

ALTER DATABASE [Expert-Only] ADD FILE   
(  
    NAME = Sales3datafile,  
    FILENAME = 
     'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Sales3df.ndf',
    SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 10MB  
)  
TO FILEGROUP Sales3filegroup;
GO  

ALTER DATABASE [Expert-Only] ADD FILE   
(  
    NAME = Sales4datafile,  
    FILENAME = 
     'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Sales4df.ndf',
    SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 10MB  
)  
TO FILEGROUP Sales4filegroup;
GO
Modifier la base et ajouter quatre fichiers de données secondaires NDF pour stocker les partitions
Modifier la base et ajouter quatre fichiers de données secondaires NDF pour stocker les partitions

La quatrième étape consiste à créer un schéma de partition du SQL Serveur

En outre, l’objet schéma de partition définit comment répartir les données dans les différents groupes de fichiers. Cela permet d’améliorer les performances et la maintenance de la table.

CREATE PARTITION SCHEME sche_Partition_Sales
AS PARTITION ufn_Partition_Sales
TO (
     Sales1filegroup,
     Sales2filegroup,
     Sales3filegroup,
     Sales4filegroup
);
Ajouter un schéma de partitionnement SQL Serveur avec quatre groupes de fichiers
Ajouter un schéma de partitionnement SQL Serveur avec quatre groupes de fichiers

Mettre à jour créer une nouvelle table pour utiliser le schéma de partition SQL Serveur

Enfin, la table est partitionnée après avoir utilisé le schéma de partition et la fonction de partition. Le code crée les partitions pour la table Sales nouvellement créée. Il passe également l’année comme colonne de partitionnement.

CREATE TABLE [dbo].[SALES_Partitioned]
(
     [Year] INT,
     [MonthName] NVARCHAR(50), 
     [MonthCurrent] BIT, 
     [NumberMonth] TINYINT, 
     [EmployeeNumber] SMALLINT, 
     [NumberOfClients] INTEGER, 
     [NumberOfSales] BIGINT, 
     [Amount_ET] NUMERIC(15,5), 
     [Amount_IT] DECIMAL(15,5)
)
ON sche_Partition_Sales ([year]);
GO
Script en T-SQL pour créer une table partitionnée par année
Script en T-SQL pour créer une table partitionnée par année

Insérer des données dans la table pour utiliser les partitions

Pour insérer des données dans la table nouvellement créée en utilisant des partitions, c’est simple. Le stockage est géré par la fonction de partition et une simple instruction d’insertion fonctionne comme d’habitude.

L’objectif est d’insérer des données pour plusieurs années et de vérifier dans quelle partition les données sont stockées.

INSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2017, 'January', 10000);
INSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2018, 'January', 11000);
INSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2019, 'January', 12000);
INSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2020, 'January', 13000);
INSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2021, 'January', 14000);
INSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2022, 'January', 15000);
INSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2023, 'January', 16000);


Après avoir inséré des valeurs pour plusieurs années, de 2017 à 2023, vérifions dans quelle partition les données sont stockées. En fait, nous pouvons lister dans quel groupe de fichiers les données de vente sont stockées, et combien de lignes sont présentes.

Pour ce faire, nous utilisons principalement deux vues système : la vue système partitions et la vue dm_db_partition_stats qui affiche les statistiques sur les partitions.

select 
     object_name(ps.object_id) as TableName,
     ps.partition_number as PartitionNumber, 
     fg.name AS FileGroupName,
     row_count as [RowCount]
from     sys.dm_db_partition_stats ps, 
          sys.partitions p
    join sys.allocation_units au  
          ON au.container_id = p.hobt_id  
    join sys.filegroups fg  
          ON fg.data_space_id = au.data_space_id 
where     p.partition_id = ps.partition_id
and          ps.[object_id] in (
     select object_id('[dbo].[SALES]') 
     union all
     select object_id('[dbo].[SALES_Partitioned]')
)
order by 2;
Requête SQL Serveur pour afficher le nom des tables et des groupes de fichiers avec le nombre de lignes
Requête SQL Serveur pour afficher le nom des tables et des groupes de fichiers avec le nombre de lignes

Dans cet exemple, les données sont stockées de cette manière par le système de base de données, car nous avons sélectionné l’option RANGE RIGHT dans la fonction de partition :

  1. Sales1filegroup stocke les données dont l’année est strictement inférieure à 2020 (etc., 2017,2018,2019).
  2. Sales2filegroup stocke les données égales ou supérieures à 2020 et inférieures à 2021 (2020)
  3. Sales3filegroup contient les données égales ou supérieures à 2021 et inférieures à 2022 (2021)
  4. Sales4filegroup contient toutes les données supérieures ou égales à 2022 (2022, 2023, etc…)

Pour conclure, cet article explique comment créer une table de partition SQL Server avec une colonne de partitionnement, une fonction de partition et un schéma de partition.

Questions sur les partitions SQL Serveur

Qu’est-ce qu’une table de partition SQL Server ?

Une table de partition est une table MS SQL avec une colonne de partitionnement, elle utilise une fonction de partition et un schéma de partition. En outre, elle contient des données qui sont divisées sur le disque pour accéder directement à un seul élément de données au lieu d’accéder aux lignes complètes d’une table unique.

Comment créer une partition de table par année ?

Une table partitionnée par année utilise l’année comme colonne de partitionnement et stocke les données d’une année donnée dans une partition spécifique. En d’autres termes, l’objectif général est d’avoir les années les plus fréquemment utilisées, comme l’année dernière, l’année en cours et l’année suivante dans des partitions séparées dédiées.

Comment améliorer les performances des requêtes SQL Server ?

Pour améliorer les performances des requêtes MSSQL, la création d’une partition de table et l’ajout d’index sont très utiles. De plus, le partitionnement permet de diviser physiquement les données en différents morceaux, ce qui permet au système de répondre plus rapidement.

Quels sont les inconvénients des partitions de SQL Server ?

Les principaux inconvénients des partitions de table MS SQL sont la maintenance des objets pour maintenir les définitions de partition à jour avec la colonne de partitionnement.

Pour aller plus loin, voyons comment créer une vue partitionnée SQL Serveur pour afficher les lignes de plusieurs tables.

Soyez le premier à commenter

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée.


*