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

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

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

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

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 (2024);
GO

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;

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

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

Vue partitionnée Sql Server pour regrouper plusieurs tables