[SQL Server] T-SQL : DELETE vs TRUNCATE

Ce billet compare les instructions DELETE et TRUNCATE en T-SQL.

Présentation basique de DELETE et de TRUNCATE

DELETE et TRUNCATE sont respectivement des opérations DML (opérations de manipulation de données) et DDL (définition de données). Leur finalité est de permettre la suppression d’enregistrements au sein d’une table.

La syntaxe d’utilisation d’une instruction :

  • DELETE (syntaxe volontairement simplifiée au possible) :
DELETE table_or_view
FROM table_source(s)
WHERE search_condition
[ ;]
  • TRUNCATE est la suivante :
TRUNCATE TABLE
[ { database_name .[ schema_name ] . | schema_name . } ]
table_name
[ ; ]

Différences entre DELETE et TRUNCATE

Le tableau ci-dessous liste les principales différences entre les instructions DELETE et TRUNCATE :

Cas de figure DELETE TRUNCATE
Peut être utilisé sur une vue (avec quelques limitations…). Oui. Non.
Un trigger peut être exécuté sur une instruction DELETE/TRUNCATE. Oui. Non.
Compatible avec la réplication. Oui. Non.
Le filtre d’enregistrements à traiter est possible (via WHERE). Oui. Non.
Réinitialise les valeurs d’identité d’une table. Non. Oui.
Possibilité de suppression en cascade. Oui. Non.
Journalise individuellement les lignes d’enregistrement supprimées. Oui. Non.
Possibilité de verrouillage de lignes. Oui. Non.
Suppression d’enregistrements d’une table contenant une clé étrangère. Oui. Oui et non.

La différence notable entre DELETE et TRUNCATE est qu’un TRUNCATE supprimer les données en désallouant les pages de données concernées de la table cible, là où un DELETE supprime une ligne de données à la fois.

De plus, là où un TRUNCATE ne peut traiter que tout le contenu d’une table sans possibilité d’utilisation d’un filtre, un DELETE peut supprimer un sous-ensemble spécifique d’enregistrements.

Au niveau du journal des transactions, comme le tableau ci-dessus l’indique, les opérations de troncature de pages de données ne journalisent pas chaque ligne supprimée, contrairement à un DELETE. En revanche, seules les désallocations de pages de données le sont.

Côté verrouillage, contrairement à un DELETE, un TRUNCATE ne permet pas de poser un verrou au niveau de chaque ligne de données traitée. A contrario, seul le verrouillage au niveau de la table et des pages de données est supporté.

Notez qu’un TRUNCATE ne fonctionne que si une table ne possède pas de clé étrangère, à moins que cette clé est une référence à elle-même.

Conseils d’utilisation

Avant de commencer, procédons par un test très basique pour chaque instruction de suppression (DELETE et TRUNCATE). Pour ce faire, créons une table Employee et insérons-y 500 000 enregistrements :

CREATE TABLE Employee(EmployeeID INT IDENTITY, EmployeeName VARCHAR(20))
GO
INSERT INTO Employee(EmployeeName)
 VALUES ('Emp')
GO 500000

Supprimons tout le contenu de la table :

  • Dans le cas d’un DELETE :
DELETE
FROM Employee
GO
  • Dans le cas d’un TRUNCATE :
TRUNCATE TABLE Employee
GO

Sur la machine de test (8 Go RAM, SSD, quadcore,…), cela donne 6 secondes pour le DELETE et… 0 pour la troncature.

Ce que l’on peut généralement déduire, c’est qu’un TRUNCATE est plus rapide qu’un DELETE, dans la mesure où il se contente simplement de vider les pages de données au lieu de supprimer les lignes une par une.

Par conséquent, pour ce qui est des conseils d’utilisation, privilégiez l’utilisation d’un TRUNCATE si ses limitations présentées dans la section précédente ne sont pas rédhibitoires et que l’absence de possibilité de rollback n’est guère problématique.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s