[SQL Server] Partitionnement d'une table SQL Server
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('MaTable_1','U') IS NOT NULL
BEGIN
DROP TABLE MaTable_1;
END
IF OBJECT_ID('MaTable_2','U') IS NOT NULL
BEGIN
DROP TABLE MaTable_2;
END
IF EXISTS (SELECT name FROM sys.partition_schemes WHERE name = 'PS_PartitionToPrimary')
BEGIN
DROP PARTITION SCHEME PS_PartitionToPrimary
END
IF EXISTS (SELECT name FROM sys.partition_functions WHERE name = 'PF_PartitionByMonth')
BEGIN
DROP PARTITION FUNCTION PF_PartitionByMonth
END
CREATE PARTITION FUNCTION PF_PartitionByMonth (DATE)
AS RANGE RIGHT
FOR VALUES ('2024/01/01', '2024/02/01', '2024/03/01', '2024/04/01', '2024/05/01','2024/06/01'
, '2024/07/01', '2024/08/01', '2024/09/01', '2024/10/01', '2024/11/01', '2024/12/01');
CREATE PARTITION SCHEME PS_PartitionToPrimary AS PARTITION PF_PartitionByMonth
ALL TO ([PRIMARY]);
CREATE TABLE MaTable_1 (
num_identite INT IDENTITY(1,1)
,nom_etudiant VARCHAR(50)
,date_naissance DATE
,inscrit_etablissement BIT DEFAULT(0))
CREATE TABLE MaTable_2 (
num_identite INT IDENTITY(1,1)
,nom_etudiant VARCHAR(50)
,date_naissance DATE
,inscrit_etablissement BIT DEFAULT(0)) ON PS_PartitionToPrimary (date_naissance)
--CREATE CLUSTERED INDEX idx_hubba_date_naissance ON MaTable_1 (date_naissance)
--ON PS_PartitionToPrimary (date_naissance);
--GO
/*
Établir une plage de dates à utiliser pour la génération de dates aléatoires et le remplissage de la table
Nous n'avons que la table partitionnée pour l'année en cours, limitant donc les dates à celle-ci.
l'année est essentielle
*/
DECLARE @BeginDate DATE = '2024-01-01'
,@EndDate DATE = '2024-12-31'
INSERT INTO MaTable_1
( nom_etudiant,date_naissance )
VALUES ( 'TestPartionnementTable'
,DATEADD(DAY,RAND(CHECKSUM(NEWID())) * ( 1 + DATEDIFF(DAY, @EndDate,@BeginDate) ), @EndDate))
GO 5000
USE tempdb;
GO
sp_help 'MaTable_1'
/* Démonstration l'existence de
2 contraintes sur la table temporaire
2 index sur la table temporaire
1 cluster (prend en charge la partition)
1 non clusterisé
La requête montre que des partitions peuvent être créées sur des tables temporaires
*/
SELECT *
FROM MaTable_1;
SELECT OBJECT_NAME(T6.object_id) AS object_name ,
T4.name AS pf_name ,T3.name AS partition_scheme_name ,
T7.partition_number ,T5.value AS range_value ,
SUM(CASE WHEN T6.index_id IN ( 1, 0 ) THEN T7.rows
ELSE 0
END) AS num_rows ,
SUM(T8.reserved_page_count) * 8 / 1024. AS reserved_mb_all_indexes ,
SUM(CASE ISNULL(T6.index_id, 0)
WHEN 0 THEN 0
ELSE 1
END) AS num_indexes
FROM sys.destination_data_spaces AS T1
INNER JOIN sys.data_spaces AS T2 ON T1.data_space_id = T2.data_space_id
INNER JOIN sys.partition_schemes AS T3 ON T1.partition_scheme_id = T3.data_space_id
INNER JOIN sys.partition_functions AS T4 ON T3.function_id = T4.function_id
LEFT OUTER JOIN sys.partition_range_values AS T5 ON T4.function_id = T5.function_id
AND T1.destination_id = CASE T4.boundary_value_on_right WHEN 0 THEN T5.boundary_id ELSE T5.boundary_id + 1 END
LEFT OUTER JOIN sys.indexes AS T6 ON T1.partition_scheme_id = T6.data_space_id
LEFT OUTER JOIN sys.partitions AS T7 ON T6.object_id = T7.object_id AND T6.index_id = T7.index_id AND T1.destination_id = T7.partition_number
LEFT OUTER JOIN sys.dm_db_partition_stats AS T8 ON T7.object_id = T8.object_id AND T7.partition_id = T8.partition_id
WHERE T7.OBJECT_ID = OBJECT_ID('MaTable_1','U')
GROUP BY T7.partition_number ,T4.name,T3.name ,T6.object_id ,T5.value;
/*
TRUNCATE TABLE MaTable_1
WITH (PARTITIONS (2));
TRUNCATE TABLE MaTable_1
WITH (PARTITIONS (3));
TRUNCATE TABLE MaTable_1
WITH (PARTITIONS (2, 4, 6 TO 8));
*/
select * from MaTable_1 where date_naissance<='20240131' --405
select * from MaTable_2
ALTER TABLE MaTable_1
SWITCH PARTITION 3 TO MaTable_2
ALTER TABLE MaTable_1
SWITCH TO MaTable_2 PARTITION 3
ALTER TABLE MaTable_1
SWITCH TO MaTable_2 PARTITION $PARTITION.PF_PartitionByMonth ('2024/02/01')
SELECT
p.partition_number AS partition_number,
f.name AS file_group,
p.rows AS row_count
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_NAME(OBJECT_ID) = 'MaTable_1'
order by partition_number;
select
ROW_NUMBER() OVER (ORDER BY $PARTITION.PF_PartitionByMonth(t.date_naissance)) as date_naissance
,$PARTITION.PF_PartitionByMonth(t.date_naissance) as Partition
,COUNT(t.date_naissance) AS Nb
,MIN(t.num_identite) as Min_ID
,MAX(t.num_identite) as Max_ID
,t.date_naissance
from [dbo].[MaTable_1] t
GROUP by $PARTITION.PF_PartitionByMonth(t.date_naissance), t.date_naissance
DECLARE @numPartition AS INT
select @numPartition=$PARTITION.PF_PartitionByMonth(date_naissance)
from [dbo].[MaTable_1] (nolock)
where date_naissance = '2024/01/01'
truncate table [dbo].[MaTable_1] with (partitions(@numPartition) )