{"id":9229,"date":"2022-08-02T07:06:00","date_gmt":"2022-08-02T05:06:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=9229"},"modified":"2022-11-30T10:40:04","modified_gmt":"2022-11-30T09:40:04","slug":"table-sql-server-avec-partitions","status":"publish","type":"post","link":"https:\/\/expert-only.com\/fr\/t-sql\/table-sql-server-avec-partitions\/","title":{"rendered":"Cr\u00e9er une table SQL Server avec des partitions"},"content":{"rendered":"\n

Comment cr\u00e9er une table avec des partition pour dans SQL Server ? Consid\u00e9rons une grande table stock\u00e9e dans une base SQL Serveur contenant des donn\u00e9es de ventes. Cette table contient une colonne qui stocke l’ann\u00e9e de la vente et la table stocke des millions de lignes. <\/p>\n\n\n\n

Consid\u00e9rons \u00e9galement que certains rapports lisent ces donn\u00e9es pour afficher les totaux annuels et les comparent \u00e0 l’ann\u00e9e pr\u00e9c\u00e9dente.<\/p>\n\n\n\n

Scripts pour cr\u00e9er une table SQL Server avec des partitions \u00e9tape par \u00e9tape<\/h2>\n\n\n\n

Pour commencer, utilisons cette table de ventes comme exemple pour la cr\u00e9ation de la partition.<\/p>\n\n\n\n

CREATE TABLE [dbo].[SALES]\n(\n     [Year] INT,\n     [MonthName] NVARCHAR(50), -- alphanumeric\n     [MonthCurrent] BIT, -- Boolean, 0 or 1 , false \/ true\n     [NumberMonth] TINYINT, -- very small integer, from 0 to 255\n     [EmployeeNumber] SMALLINT, -- small integer, minimum -2^15, maximum 2^15\n     [NumberOfClients] INTEGER, -- integer, minimum -2^31, maximum 2^31\n     [NumberOfSales] BIGINT, -- big integer, minimum: -2^63, maximum 2^63\n     [Amount_ET] NUMERIC(15,5), -- numeric, 15 digits, with 5 after the comma\n     [Amount_IT] DECIMAL(15,5) -- decimal, 15 digits, with 5 after the comma\n);\nGO<\/pre>\n\n\n\n
Pour clarifier, le partitionnement d’une table se fait avec quatre objets principaux et quatre d\u00e9cisions \u00e0 prendre. La d\u00e9cision principale est de choisir la colonne de partitionnement.<\/p>\n\n\n\n
    \n
  1. La premi\u00e8re \u00e9tape consiste \u00e0 choisir la colonne de partitionnement<\/li>\n\n\n\n
  2. La deuxi\u00e8me \u00e9tape consiste \u00e0 cr\u00e9er une fonction de partitionnement<\/li>\n\n\n\n
  3. La troisi\u00e8me \u00e9tape consiste \u00e0 cr\u00e9er les groupes de fichiers n\u00e9cessaires<\/li>\n\n\n\n
  4. La quatri\u00e8me \u00e9tape consiste \u00e0 cr\u00e9er un sch\u00e9ma de partitionnement<\/li>\n\n\n\n
  5. Derni\u00e8re \u00e9tape : mettre \u00e0 jour la table existante pour utiliser le sch\u00e9ma de partitionnement.<\/li>\n<\/ol>\n\n\n\n
    La premi\u00e8re \u00e9tape consiste \u00e0 choisir la colonne de partitionnement<\/h3>\n\n\n\n
    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\u00eates utilisent l’ann\u00e9e comme filtre pour afficher les chiffres annuels et les comparaisons d’une ann\u00e9e sur l’autre.<\/p>\n\n\n\n
    Le choix d’une partition sur la colonne ann\u00e9e est int\u00e9ressant. La colonne de partitionnement est la colonne Ann\u00e9e et c’est un entier.<\/p>\n\n\n\n
    Les diff\u00e9rentes partitions sur l’ann\u00e9e vont r\u00e9partir physiquement les donn\u00e9es du disque sur plusieurs groupes de fichiers. Cette op\u00e9ration est \u00e9galement appel\u00e9e partitionnement d’une table existante. Vous trouverez plus de d\u00e9tails dans cet article sur la d\u00e9finition d’une partition SQL Server.<\/p>\n\n\n\n
    De plus, la table reste un objet logique unique de SQLServeur, mais physiquement elle est divis\u00e9e en diff\u00e9rents fichiers pour isoler et am\u00e9liorer l’acc\u00e8s \u00e0 chaque partition.<\/p>\n\n\n\n
    La deuxi\u00e8me \u00e9tape consiste \u00e0 cr\u00e9er une fonction de partition SQL Server<\/h3>\n\n\n\n
    Ensuite, pour cr\u00e9er une fonction de partition, il suffit d’utiliser une instruction CREATE afin de cr\u00e9er la fonction de partition. Les param\u00e8tres sont le type de donn\u00e9es de la colonne de partitionnement, le type de partition qui peut \u00eatre droite ou gauche, et la plage de valeurs.<\/p>\n\n\n\n
    Par exemple, cet exemple de code T-SQL montre comment cr\u00e9er une fonction de partition bas\u00e9e sur les ann\u00e9es pr\u00e9c\u00e9dentes, la derni\u00e8re ann\u00e9e, l’ann\u00e9e en cours et les ann\u00e9es suivantes.<\/p>\n\n\n\n
    CREATE PARTITION FUNCTION ufn_Partition_Sales (int)\nAS RANGE RIGHT\nFOR VALUES (\n     2018,\n     2019,\n     2020\n);<\/pre>\n\n\n
    \n
    \"Script
    Script T-SQL pour cr\u00e9er une fonction de partition avec l’option range right comme sch\u00e9ma de partition<\/figcaption><\/figure><\/div>\n\n\n
    La troisi\u00e8me \u00e9tape est de cr\u00e9er les groupes de fichiers n\u00e9cessaires pour les partitions<\/h3>\n\n\n\n
    De plus, un sch\u00e9ma de partition utilise une fonction de partition et un sch\u00e9ma de partition utilise des groupes de fichiers. Afin de cr\u00e9er le sch\u00e9ma<\/a> de partition, cr\u00e9ez d’abord les groupes de fichiers \u00e0 utiliser.<\/p>\n\n\n\n
    Avant tout, assurez-vous d’ajuster le code avant de l’ex\u00e9cuter. Utilisez le code ci-dessous pour cr\u00e9er quatre groupes de fichiers suppl\u00e9mentaires \u00e0 la base de donn\u00e9es Expert-Only existante. Ensuite, l’exemple de code suivant cr\u00e9e les groupes de fichiers dans le syst\u00e8me de fichiers. En outre, consultez la documentation officielle sur les tables et les index partitionn\u00e9s.<\/p>\n\n\n\n
    USE [Expert-Only];  \nGO  \n\nALTER DATABASE [Expert-Only] ADD FILEGROUP Sales1filegroup;  \nGO  \n\nALTER DATABASE [Expert-Only] ADD FILEGROUP Sales2filegroup;  \nGO  \n\nALTER DATABASE [Expert-Only] ADD FILEGROUP Sales3filegroup;  \nGO  \n\nALTER DATABASE [Expert-Only] ADD FILEGROUP Sales4filegroup;  \nGO<\/pre>\n\n\n
    \n
    \"Modifier
    Modifier la base de donn\u00e9es pour ajouter des groupes de donn\u00e9es logiques<\/figcaption><\/figure><\/div>\n\n\n
    En outre, l’\u00e9tape suivante est obligatoire et attribue les fichiers physiques aux groupes de fichiers logiques existants. Pour ce faire, ajoutons un fichier de donn\u00e9es secondaire \u00e0 chaque groupe de fichiers cr\u00e9\u00e9.<\/p>\n\n\n\n
    ALTER DATABASE [Expert-Only] ADD FILE   \n(  \n    NAME = Sales1datafile,  \n    FILENAME = \n     'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\DATA\\Sales1df.ndf',\n    SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 10MB  \n)  \nTO FILEGROUP Sales1filegroup;\nGO\n\nALTER DATABASE [Expert-Only] ADD FILE   \n(  \n    NAME = Sales2datafile,  \n    FILENAME = \n     'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\DATA\\Sales2df.ndf',\n    SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 10MB  \n)  \nTO FILEGROUP Sales2filegroup;\nGO \n\nALTER DATABASE [Expert-Only] ADD FILE   \n(  \n    NAME = Sales3datafile,  \n    FILENAME = \n     'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\DATA\\Sales3df.ndf',\n    SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 10MB  \n)  \nTO FILEGROUP Sales3filegroup;\nGO  \n\nALTER DATABASE [Expert-Only] ADD FILE   \n(  \n    NAME = Sales4datafile,  \n    FILENAME = \n     'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\DATA\\Sales4df.ndf',\n    SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 10MB  \n)  \nTO FILEGROUP Sales4filegroup;\nGO<\/pre>\n\n\n
    \n
    \"Modifier
    Modifier la base et ajouter quatre fichiers de donn\u00e9es secondaires NDF pour stocker les partitions<\/figcaption><\/figure><\/div>\n\n\n
    La quatri\u00e8me \u00e9tape consiste \u00e0 cr\u00e9er un sch\u00e9ma de partition du SQL Serveur<\/h3>\n\n\n\n
    En outre, l’objet sch\u00e9ma de partition d\u00e9finit comment r\u00e9partir les donn\u00e9es dans les diff\u00e9rents groupes de fichiers. Cela permet d’am\u00e9liorer les performances et la maintenance de la table.<\/p>\n\n\n\n
    CREATE PARTITION SCHEME sche_Partition_Sales\nAS PARTITION ufn_Partition_Sales\nTO (\n     Sales1filegroup,\n     Sales2filegroup,\n     Sales3filegroup,\n     Sales4filegroup\n);<\/pre>\n\n\n\n
    \"Ajouter
    Ajouter un sch\u00e9ma de partitionnement SQL Serveur avec quatre groupes de fichiers<\/figcaption><\/figure>\n\n\n\n
    Mettre \u00e0 jour cr\u00e9er une nouvelle table pour utiliser le sch\u00e9ma de partition SQL Serveur<\/h3>\n\n\n\n
    Enfin, la table est partitionn\u00e9e apr\u00e8s avoir utilis\u00e9 le sch\u00e9ma de partition et la fonction de partition. Le code cr\u00e9e les partitions pour la table Sales nouvellement cr\u00e9\u00e9e. Il passe \u00e9galement l’ann\u00e9e comme colonne de partitionnement.<\/p>\n\n\n\n
    CREATE TABLE [dbo].[SALES_Partitioned]\n(\n     [Year] INT,\n     [MonthName] NVARCHAR(50), \n     [MonthCurrent] BIT, \n     [NumberMonth] TINYINT, \n     [EmployeeNumber] SMALLINT, \n     [NumberOfClients] INTEGER, \n     [NumberOfSales] BIGINT, \n     [Amount_ET] NUMERIC(15,5), \n     [Amount_IT] DECIMAL(15,5)\n)\nON sche_Partition_Sales (2026);\nGO<\/pre>\n\n\n
    \n
    \"Script
    Script en T-SQL pour cr\u00e9er une table partitionn\u00e9e par ann\u00e9e<\/figcaption><\/figure><\/div>\n\n\n
    Ins\u00e9rer des donn\u00e9es dans la table pour utiliser les partitions<\/h2>\n\n\n\n
    Pour ins\u00e9rer des donn\u00e9es dans la table nouvellement cr\u00e9\u00e9e en utilisant des partitions, c’est simple. Le stockage est g\u00e9r\u00e9 par la fonction de partition et une simple instruction d’insertion fonctionne comme d’habitude.<\/p>\n\n\n\n
    L’objectif est d’ins\u00e9rer des donn\u00e9es pour plusieurs ann\u00e9es et de v\u00e9rifier dans quelle partition les donn\u00e9es sont stock\u00e9es.<\/p>\n\n\n\n
    INSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2017, 'January', 10000);\nINSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2018, 'January', 11000);\nINSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2019, 'January', 12000);\nINSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2020, 'January', 13000);\nINSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2021, 'January', 14000);\nINSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2022, 'January', 15000);\nINSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2023, 'January', 16000);\n<\/pre>\n\n\n\n

    Apr\u00e8s avoir ins\u00e9r\u00e9 des valeurs pour plusieurs ann\u00e9es, de 2017 \u00e0 2023, v\u00e9rifions dans quelle partition les donn\u00e9es sont stock\u00e9es. En fait, nous pouvons lister dans quel groupe de fichiers les donn\u00e9es de vente sont stock\u00e9es, et combien de lignes sont pr\u00e9sentes.<\/p>\n\n\n\n
    Pour ce faire, nous utilisons principalement deux vues syst\u00e8me : la vue syst\u00e8me partitions et la vue dm_db_partition_stats qui affiche les statistiques sur les partitions.<\/p>\n\n\n\n
    select \n     object_name(ps.object_id) as TableName,\n     ps.partition_number as PartitionNumber, \n     fg.name AS FileGroupName,\n     row_count as [RowCount]\nfrom     sys.dm_db_partition_stats ps, \n          sys.partitions p\n    join sys.allocation_units au  \n          ON au.container_id = p.hobt_id  \n    join sys.filegroups fg  \n          ON fg.data_space_id = au.data_space_id \nwhere     p.partition_id = ps.partition_id\nand          ps.[object_id] in (\n     select object_id('[dbo].[SALES]') \n     union all\n     select object_id('[dbo].[SALES_Partitioned]')\n)\norder by 2;<\/pre>\n\n\n
    \n
    \"Requ\u00eate
    Requ\u00eate SQL Serveur pour afficher le nom des tables et des groupes de fichiers avec le nombre de lignes<\/figcaption><\/figure><\/div>\n\n\n
    Dans cet exemple, les donn\u00e9es sont stock\u00e9es de cette mani\u00e8re par le syst\u00e8me de base de donn\u00e9es, car nous avons s\u00e9lectionn\u00e9 l’option RANGE RIGHT dans la fonction de partition :<\/p>\n\n\n\n
      \n
    1. Sales1filegroup stocke les donn\u00e9es dont l’ann\u00e9e est strictement inf\u00e9rieure \u00e0 2020 (etc., 2017,2018,2019).<\/li>\n\n\n\n
    2. Sales2filegroup stocke les donn\u00e9es \u00e9gales ou sup\u00e9rieures \u00e0 2020 et inf\u00e9rieures \u00e0 2021 (2020)<\/li>\n\n\n\n
    3. Sales3filegroup contient les donn\u00e9es \u00e9gales ou sup\u00e9rieures \u00e0 2021 et inf\u00e9rieures \u00e0 2022 (2021)<\/li>\n\n\n\n
    4. Sales4filegroup contient toutes les donn\u00e9es sup\u00e9rieures ou \u00e9gales \u00e0 2022 (2022, 2023, etc\u2026)<\/li>\n<\/ol>\n\n\n\n
      Pour conclure, cet article explique comment cr\u00e9er une table de partition SQL Server avec une colonne de partitionnement, une fonction de partition et un sch\u00e9ma de partition.<\/p>\n\n\n\n
      Questions sur les partitions SQL Serveur<\/h3>\n\n\n\n
      Qu’est-ce qu’une table de partition SQL Server ?<\/strong> 
      Une table de partition est une table MS SQL avec une colonne de partitionnement, elle utilise une fonction de partition et un sch\u00e9ma de partition. En outre, elle contient des donn\u00e9es qui sont divis\u00e9es sur le disque pour acc\u00e9der directement \u00e0 un seul \u00e9l\u00e9ment de donn\u00e9es au lieu d’acc\u00e9der aux lignes compl\u00e8tes d’une table unique.<\/p> <\/div> 
      Comment cr\u00e9er une partition de table par ann\u00e9e ?<\/strong> 
      Une table partitionn\u00e9e par ann\u00e9e utilise l’ann\u00e9e comme colonne de partitionnement et stocke les donn\u00e9es d’une ann\u00e9e donn\u00e9e dans une partition sp\u00e9cifique. En d’autres termes, l’objectif g\u00e9n\u00e9ral est d’avoir les ann\u00e9es les plus fr\u00e9quemment utilis\u00e9es, comme l’ann\u00e9e derni\u00e8re, l’ann\u00e9e en cours et l’ann\u00e9e suivante dans des partitions s\u00e9par\u00e9es d\u00e9di\u00e9es.<\/p> <\/div> 
      Comment am\u00e9liorer les performances des requ\u00eates SQL Server ?<\/strong> 
      Pour am\u00e9liorer les performances des requ\u00eates MSSQL, la cr\u00e9ation d’une partition de table et l’ajout d’index sont tr\u00e8s utiles. De plus, le partitionnement permet de diviser physiquement les donn\u00e9es en diff\u00e9rents morceaux, ce qui permet au syst\u00e8me de r\u00e9pondre plus rapidement.<\/p> <\/div> 
      Quels sont les inconv\u00e9nients des partitions de SQL Server ?<\/strong> 
      Les principaux inconv\u00e9nients des partitions de table MS SQL sont la maintenance des objets pour maintenir les d\u00e9finitions de partition \u00e0 jour avec la colonne de partitionnement.<\/p> <\/div> <\/div>\n\n\n\n
      Pour aller plus loin, voyons comment cr\u00e9er une vue partitionn\u00e9e SQL Serveur pour afficher les lignes de plusieurs tables<\/strong><\/a>.<\/p>\n\n\n\n
      \n
      Cr\u00e9er une vue partitionn\u00e9e SQL Server pour regrouper des tables<\/a><\/blockquote>