Supprimer de gros volumes de données peut être problématique si l’on se cantonne à utiliser l’instruction DELETE.
Cette dernière a le défaut de poser un verrou exclusif sur la table cible et comme toute transaction sur les données de journaliser les opérations dans le fichier transaction log afin de pouvoir rollback la transaction en cas de problème et d’assurer l’intégrité des données.
Si vous avez besoin de nettoyer une table de données sans supprimer la totalité de la table (sinon l’instruction TRUNCATE TABLE ferait l’affaire), il peut être judicieux d’utiliser les fonctionnalités du partitionnement.
L’idée directrice de cette méthode et d’affecter les données d’une table source partitionnée à une table cible partitionnée sur le même schéma de partitionnement. Dans la mesure où physiquement rien n’est ni modifié ni déplacé et que l’opération porte juste sur les métadonnées, le temps d’exécution de cette manipulation est quasiment instantanée.
Une fois le Switch de partition opéré vous pouvez très facilement lancer l’instruction TRUNCATE TABLE sur la table cible.
Petit rappel : les fonctionnalités de partitionnement sont disponibles à partir de la version 2005 de SQL SERVER.
Ci-dessous je vous ai préparé un petit script d’exemple pour vous illustrer cette manipulation. La manip est faite dans les étapes 7 et 8, avant il ne s’agit que de la préparation de la base.
----------------------------------------------------------------------------------------- -- ETAPE 01: Création de la base de données ----------------------------------------------------------------------------------------- Create Database DemoPartitionsDatabase Go ----------------------------------------------------------------------------------------- -- ETAPE 02: Création de la fonction de partition et scheme ----------------------------------------------------------------------------------------- Use DemoPartitionsDatabase Go Create Partition Function MaFonctionDePartionnement (int) As Range LEFT For Values (20110725,20110801) Go Create Partition Scheme MaFonctionScheme As Partition MaFonctionDePartionnement ALL To ([PRIMARY]) Go ----------------------------------------------------------------------------------------- -- ETAPE 03: Création de la table ----------------------------------------------------------------------------------------- Create table FactTable ( [date] int not null, col1 int null, col2 int null, col3 int null, col4 int null, col5 int null, col6 int null, col7 int null, col8 int null, col9 int null, ) ON MaFonctionScheme([date]) ----------------------------------------------------------------------------------------- -- ETAPE 04: Mettons un peu de volumétrie dans la table ----------------------------------------------------------------------------------------- declare @startdate date='2011-07-25', @i int=0 While @i<9 begin ;with t0 as (select n = 1 union all select n = 1), t1 as (select n = 1 from t0 as a, t0 as b), t2 as (select n = 1 from t1 as a, t1 as b), t3 as (select n = 1 from t2 as a, t2 as b), t4 as (select n = 1 from t3 as a, t3 as b), t5 as (select n = 1 from t4 as a, t4 as b), result as (select top 1000000 row_number() over (order by n) as n from t5) insert into FactTable select (select convert(int,convert(varchar(10),dateadd(dd,@i,@startdate),112))), n,n,n,n,n,n,n,n,n from result set @i=@i+1 end go ----------------------------------------------------------------------------------------- -- ETAPE 05: Creation d'une vue pour suivre l'état du partitionnement ----------------------------------------------------------------------------------------- create view [dbo].[partition_info] as SELECT OBJECT_NAME(p.object_id) AS ObjectName, i.name AS IndexName,p.index_id AS IndexID,ds.name AS PartitionScheme,p.partition_number AS PartitionNumber,fg.name AS FileGroupName,prv_left.value AS LowerBoundaryValue,prv_right.value AS UpperBoundaryValue, CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS Range, p.rows AS Rows FROM sys.partitions AS p JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id JOIN sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number JOIN sys.filegroups AS fg ON fg.data_space_id = dds2.data_space_id LEFT JOIN sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1 LEFT JOIN sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number WHERE OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0 select * from [dbo].[partition_info] go ----------------------------------------------------------------------------------------- -- ETAPE 06: Crééons une table de purge identique à la première ----------------------------------------------------------------------------------------- Create table FactTablePurge ( [date] int not null, col1 int null, col2 int null, col3 int null, col4 int null, col5 int null, col6 int null, col7 int null, col8 int null, col9 int null, ) ON MaFonctionScheme([date]) ----------------------------------------------------------------------------------------- -- ETAPE 07: Déplaçons (que dis-je affectons) les données à la table de purge ----------------------------------------------------------------------------------------- alter table dbo.FactTable switch partition 2 to dbo.FactTablePurge partition 2 go select * from dbo.partition_info go ----------------------------------------------------------------------------------------- -- ETAPE 08: Il ne reste plus qu'à truncate la table de purge et à regarder le résultat ----------------------------------------------------------------------------------------- truncate table dbo.FactTablePurge go select * from dbo.partition_info go /***************************************************************************************** ** The End : Les données ont disparu instantannément sans trace dans le fichier de log *****************************************************************************************/ /***** Si vous n'êtes pas convaincu par cette méthode essayez d'éxécuter le delete sur les 7 millions de lignes avec la requête ci-dessous --delete from FactTable where $partition.MaFonctionDePartionnement(date)=2 *******/