DELETE ou TRUNCATE ?

 En SQL, les commandes DELETE et TRUNCATE qui ont pour but de vider une table ont cependant des différences. Cet article a pour but de lister ces différences afin de mieux comprendre le comportement de ces deux commandes.

Syntaxe :

Pour DELETE :

DELETE FROM Clients WHERE Age < 30;

Pour TRUNCATE :

TRUNCATE TABLE Clients;

La commande TRUNCATE ne supporte pas la clause WHERE comme DELETE. En effet, TRUNCATE supprime toutes les lignes de la table sans condition, ce qui le rend beaucoup plus rapide que DELETE pour vider une table entière.

Journalisation des commandes SQL :

Les bases de données relationnelles peuvent utiliser un journal qui enregistre les modifications apportées aux données. Il ne s’agit pas par exemple de garder en mémoire des lignes qui auraient été supprimées afin de les récupérer plus tard. Cette action est possible autrement mais ce n’ai pas le but de la journalisation. Le but de la journalisation est de restaurer une table si une panne surviens pendant l’éxécution de la requête ou bien de restaurer une table dans le contexte d’une transaction.

Avec DELETE, la journalisation s’effectue ligne par ligne ce qui permet de gérer les pannes durant l’exécution de la requête et de récupérer la table d’origine. Cela permet également le bon fonctionnement de ROLLBACK ou de COMMIT dans le cadre d’une transaction et de conserver la table d’origine.

Avec TRUNCATE, la journalisation est minimaliste ce qui ne permet pas de gérer les pannes et ce qui ne permet pas le même fonctionnement de ROLLBACK ou COMMIT avec DELETE. Autrement dit la table d’origine de sera pas récupérée.

Impact sur la performance :

Avec DELETE chaque suppression est une opération individuelle enregistrée dans le journal. Si la table est importante la commande DELETE sera lente.

Avec TRUNCATE l’opération est beaucoup plus rapide.

Incrémentation de la colonne d’identifiant :

Avec DELETE une fois les lignes supprimées, la colonne d’identifiant, si elle subit une auto incrémentation, va continuer d’être incrémentée en suivant l’odre logique de la dernière ligne insérée.

Avec TRUNCATE l’auto incrémentation de la colonne d’identifiant recommence à sa valeur d’initialisation paramètrée lors de la création de la table. Il faut bien comprendre que TRUNCATE supprime toutes les lignes de la table. Il n’y a pas de clause WHERE avec un TRUNCATE.

CREATE TABLE Clients (ID_client INT IDENTITY(1,1) PRIMARY KEY, Nom NVARCHAR(100));

Utilisation des Triggers :

Uniquement avec DELETE.

Gestion des contraintes de clés étrangères :

DELETE respecte les contraintes de clé étrangère. Si on essaye de supprimer une ligne dans une table qui est référencée par une clé étrangère dans une autre table, on obtien une erreur, sauf si la contrainte de clé étrangère permet une action en cascade (comme ON DELETE CASCADE).

CREATE TABLE Clients (

ClientID INT IDENTITY(1,1) PRIMARY KEY,

Nom NVARCHAR(100));

CREATE TABLE Commandes (

CommandeID INT IDENTITY(1,1) PRIMARY KEY,

ClientID INT,

DateCommande DATE,

Montant FLOAT,

FOREIGN KEY (ClientID) REFERENCES Clients(ClientID) ON DELETE CASCADE );

TRUNCATE ne peut pas être exécutée si la table est référencée par une contrainte de clé étrangère, même si la contrainte est en cascade. Cela est dû au fait que TRUNCATE supprime toutes les lignes de la table de manière rapide et sans pouvoir gérer les relations avec d'autres tables.