Les meilleures façons d’utiliser l’instruction SQL Delete dans une table SQL

Dans les bases de données relationnelles, nous créons des tables pour stocker des données sous différents formats. SQL Server stocke les données dans un format de lignes et de colonnes qui contient une valeur associée à chaque type de données. Lorsque nous concevons des tables SQL, nous définissons des types de données tels que integer, float, decimal, varchar et bit. Par exemple, une table qui stocke des données sur les clients peut comporter des champs tels que le nom du client, son adresse électronique, son adresse, son état, son pays, etc. Diverses commandes SQL sont exécutées sur une table SQL et peuvent être divisées dans les catégories suivantes :

• Langage de définition de données (DDL) : Ces commandes sont utilisées pour créer et modifier les objets d’une base de données.
  • Créer : Crée des objets
  • Modifier : Modifie les objets
  • Drop : Supprime les objets
  • Truncate : Supprime toutes les données d’une table
• Langage de manipulation des données (DML) : Ces commandes insèrent, récupèrent, modifient, suppriment et mettent à jour les données dans la base de données.
  • Sélectionner : Récupère les données d’une table unique ou multiple
  • Insérer : Ajoute de nouvelles données dans une table
  • Mise à jour : Modifie les données existantes
  • Supprimer : Supprime les enregistrements existants dans une table

• Langage de contrôle des données (DCL) : Ces commandes sont associées à des contrôles de droits ou de permissions dans une base de données.
  • Accorder : Attribue des permissions à un utilisateur
  • Révoquer : Révoque les permissions d’un utilisateur
• Langage de contrôle des transactions (TCL) : Ces commandes contrôlent les transactions dans une base de données.
  • Commit : Sauvegarde les modifications apportées par la requête
  • Rollback : Annule une transaction explicite ou implicite au début de la transaction, ou à un point de sauvegarde à l’intérieur de la transaction
  • Sauvegarder les transactions : Définit un point de sauvegarde ou un marqueur à l’intérieur d’une transaction

Supposons que vous ayez des données de commandes de clients stockées dans une table SQL. Si vous continuez à insérer des données dans cette table en permanence, celle-ci pourrait contenir des millions d’enregistrements, ce qui entraînerait des problèmes de performances au sein de vos applications. La maintenance de vos index pourrait également devenir extrêmement fastidieuse. Souvent, vous n’avez pas besoin de conserver les commandes datant de plus de trois ans. Dans ce cas, vous pouvez supprimer ces enregistrements de la table. Cela permettrait d’économiser de l’espace de stockage, ainsi que de réduire vos efforts de maintenance.

Vous pouvez supprimer des données d’une table SQL de deux façons :

• Utiliser une instruction SQL delete
• Utiliser un truncate

Nous examinerons plus tard la différence entre ces commandes SQL. Explorons d’abord l’instruction SQL delete.

Une instruction SQL delete sans aucune condition

Dans les instructions du langage de manipulation des données (DML), une instruction SQL delete supprime les lignes d’une table. Vous pouvez supprimer une ligne spécifique ou toutes les lignes. Une instruction de suppression de base ne nécessite aucun argument.

Créons une table SQL Orders à l’aide du script ci-dessous. Cette table a trois colonnes , et .

Create Table Orders ( OrderID int, ProductName varchar(50), ProductQuantity int )

Insérez quelques enregistrements dans cette table.

Insert into Orders values (1,'ABC books',10), (2,'XYZ',100), (3,'SQL book',50)

Maintenant, supposons que nous voulons supprimer les données de la table. Vous pouvez spécifier le nom de la table pour supprimer les données en utilisant l’instruction delete. Les deux instructions SQL sont les mêmes. Nous pouvons spécifier le nom de la table à partir du mot clé (facultatif) ou spécifier le nom de la table directement après la suppression.

Delete Orders Go Delete from Orders GO

Une instruction SQL delete avec des données filtrées

Ces instructions SQL delete suppriment toutes les données de la table. Habituellement, nous ne supprimons pas toutes les lignes d’une table SQL. Pour supprimer une ligne spécifique, nous pouvons ajouter une clause where avec l’instruction delete. La clause where contient les critères de filtre et détermine éventuellement la ou les lignes à supprimer.

Par exemple, supposons que nous voulons supprimer l’id de commande 1. Une fois que nous ajoutons une clause where, SQL Server vérifie d’abord les lignes correspondantes et supprime ces lignes spécifiques.

Delete Orders where orderid=1

Si la condition de la clause where est fausse, il ne supprime aucune ligne. Par exemple, nous avons supprimé la commande 1 de la table des commandes. Si nous exécutons à nouveau l’instruction, elle ne trouve aucune ligne pour satisfaire la condition de la clause where. Dans ce cas, elle renvoie 0 rangée affectée.

SQL delete statement and TOP clause

Vous pouvez utiliser l’instruction TOP pour supprimer les rangées également. Par exemple, la requête ci-dessous supprime les 100 premières lignes de la table Commandes.

Delete top (100)  from Orders

Comme nous n’avons pas spécifié de ‘ORDER BY’, elle choisit des lignes aléatoires et les supprime. Nous pouvons utiliser la clause Order by pour trier les données et supprimer les lignes supérieures. Dans la requête ci-dessous, elle trie les par ordre décroissant, puis les supprime de la table.

Delete from Orders where In ( Select top 100 FROM Orders order by Desc )

Suppression de lignes basées sur une autre table

Parfois, nous avons besoin de supprimer des lignes basées sur une autre table. Cette table peut exister dans la même base de données ou non.

• Recherche de table

Nous pouvons utiliser la méthode de recherche de table ou la jointure SQL pour supprimer ces lignes. Par exemple, nous voulons supprimer les lignes de la table qui satisfont à la condition suivante:

Il doit y avoir des lignes correspondantes dans la table ..

Regardez la requête ci-dessous, ici nous avons une instruction select dans la clause where de l’instruction delete. SQL Server obtient d’abord les lignes qui satisfont l’instruction select et supprime ensuite ces lignes de la table en utilisant l’instruction SQL delete.

Delete Orders where orderid in (Select orderid  from Customer)

• SQL Join

Alternativement, nous pouvons utiliser des jointures SQL entre ces tables et supprimer les lignes. Dans la requête ci-dessous, nous joignons les tables ] avec la table. Une jointure SQL fonctionne toujours sur une colonne commune entre les tables. Nous avons une colonne qui joint les deux tables ensemble.

DELETE Orders FROM Orders o INNER JOIN Customer c ON o.orderid=c.orderid

Pour comprendre l’instruction de suppression ci-dessus, voyons le plan d’exécution réel.

Selon le plan d’exécution, il effectue un balayage de table sur les deux tables, obtient les données correspondantes et les supprime de la table Commandes.

• Expression de table commune (CTE)

Nous pouvons également utiliser une expression de table commune (CTE) pour supprimer les lignes d’une table SQL. Tout d’abord, nous définissons une CTE pour trouver la ligne que nous voulons supprimer.

Puis, nous joignons la CTE aux commandes de la table SQL et supprimons les lignes.

WITH cteOrders AS (SELECT OrderID FROM Customer WHERE CustomerID = 1 ) DELETE Orders FROM cteOrders sp INNER JOIN dbo.Orders o ON o.orderid = sp.orderid;

Impacts sur la plage d’identité

Les colonnes d’identité dans SQL Server génèrent des valeurs uniques et séquentielles pour votre colonne. Elles sont principalement utilisées pour identifier de manière unique une ligne dans la table SQL. Une colonne clé primaire est également un bon choix pour un index clusterisé dans SQL Server.

Dans le script ci-dessous, nous avons une table. Cette table a une colonne d’identité id.

Create Table Employee ( id int identity(1,1),  varchar(50) )

Nous avons inséré 50 enregistrements dans cette table qui ont généré les valeurs d’identité pour la colonne id.

Declare @id int=1 While(@id<=50) BEGIN Insert into Employee() values('Test'+CONVERT(VARCHAR,@ID)) Set @id=@id+1 END

Si nous supprimons quelques lignes de la table SQL, cela ne réinitialise pas les valeurs d’identité pour les valeurs suivantes. Par exemple, supprimons quelques lignes qui ont des valeurs d’identité de 20 à 25.

Delete from employee where id between 20 and 25

Maintenant, visualisez les enregistrements de la table.

Select * from employee where id>15

Il montre l’écart dans la plage de valeurs d’identité.

L’instruction SQL delete et le journal des transactions

SQL delete enregistre chaque suppression de ligne dans le journal des transactions. Supposons que vous ayez besoin de supprimer des millions d’enregistrements d’une table SQL. Vous ne voulez pas supprimer un grand nombre d’enregistrements en une seule transaction car cela pourrait entraîner une croissance exponentielle de votre fichier journal et votre base de données pourrait également être indisponible. Si vous annulez une transaction au milieu, cela pourrait prendre des heures pour annuler une déclaration de suppression.

Dans ce cas, vous devriez toujours supprimer des lignes par petits morceaux et commettre ces morceaux régulièrement. Par exemple, vous pouvez supprimer un lot de 10 000 lignes à la fois, le commiter et passer au lot suivant. Lorsque SQL Server commet le chunk, la croissance du journal des transactions peut être contrôlée.

Bonnes pratiques

• Vous devriez toujours effectuer une sauvegarde avant de supprimer des données.
• Par défaut, SQL Server utilise des transactions implicites et commet les enregistrements sans demander à l’utilisateur. Comme meilleure pratique, vous devriez commencer une transaction explicite en utilisant Begin Transaction. Cela vous donne le contrôle pour commiter ou annuler la transaction. Vous devriez également exécuter des sauvegardes fréquentes du journal des transactions si votre base de données est en mode de récupération complète.
• Vous voulez supprimer les données en petits morceaux pour éviter une utilisation excessive du journal des transactions. Cela évite également les blocages pour d’autres transactions SQL.
• Vous devriez restreindre les autorisations afin que les utilisateurs ne puissent pas supprimer les données. Seuls les utilisateurs autorisés doivent avoir accès à la suppression des données d’une table SQL.
• Vous voulez exécuter l’instruction delete avec une clause where. Elle supprime les données filtrées d’une table SQL. Si votre application nécessite une suppression fréquente des données, il est judicieux de réinitialiser périodiquement les valeurs d’identité. Sinon, vous pourriez être confronté à des problèmes d’épuisement des valeurs d’identité.
• Dans le cas où vous voulez vider la table, il est conseillé d’utiliser l’instruction truncate. L’instruction truncate supprime toutes les données d’une table, utilise une journalisation minimale des transactions, réinitialise la plage de valeurs d’identité et est plus rapide que l’instruction SQL delete car elle désalloue immédiatement toutes les pages de la table.
• Dans le cas où vous utilisez des contraintes de clé étrangère (relation parent-enfant) pour vos tables, vous devez supprimer la ligne d’une ligne enfant puis de la table parent. Si vous supprimez la rangée de la rangée parent, vous pouvez également utiliser l’option de cascade à la suppression pour supprimer automatiquement la rangée d’une table enfant. Vous pouvez vous référer à l’article : Cascade de suppression et cascade de mise à jour dans la clé étrangère de SQL Server pour plus de précisions.
• Si vous utilisez l’instruction top pour supprimer les lignes, SQL Server supprime les lignes de manière aléatoire. Vous devriez toujours utiliser la clause top avec la clause Order by et Group by correspondante.
• Une instruction de suppression acquiert un verrou exclusif d’intention sur la table de référence ; par conséquent, pendant ce temps, aucune autre transaction ne peut modifier les données. Vous pouvez utiliser l’indice NOLOCK pour lire les données.
• Vous devez éviter d’utiliser l’indice de table pour remplacer le comportement de verrouillage par défaut de l’instruction SQL delete ; il ne doit être utilisé que par des DBA et des développeurs expérimentés.

Considérations importantes

L’utilisation des instructions SQL delete pour supprimer les données d’une table SQL présente de nombreux avantages, mais comme vous pouvez le constater, elle nécessite une approche méthodique. Il est important de toujours supprimer les données par petits lots et de procéder avec prudence lorsque vous supprimez des données d’une instance de production. Disposer d’une stratégie de sauvegarde pour récupérer les données en un minimum de temps est une nécessité pour éviter les temps d’arrêt ou les impacts futurs sur les performances.