TRUNCATE TABLE (Transact-SQL)

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Warehouse dans microsoft Fabric SQL Database dans Microsoft Fabric SQL Database dans Microsoft Fabric

Supprime toutes les lignes d’une table ou des partitions spécifiées d’une table, sans journaliser les suppressions de ligne individuelles. TRUNCATE TABLE est similaire à l’instruction DELETE sans WHERE clause ; toutefois, TRUNCATE TABLE elle est plus rapide et utilise moins de ressources système et de journal des transactions.

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe pour SQL Server, Azure SQL Database, Fabric SQL Database

TRUNCATE TABLE
    { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }
    [ , ...n ] ) ) ]
[ ; ]

<range> ::=
<partition_number_expression> TO <partition_number_expression>

Syntaxe pour Microsoft Fabric, Azure Synapse Analytics et Parallel Data Warehouse.

TRUNCATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ; ]

Arguments

database_name

Nom de la base de données.

schema_name

Nom du schéma auquel appartient la table.

table_name

Nom de la table à tronquer ou à partir de laquelle toutes les lignes sont supprimées. table_name doit être un littéral. table_name ne peut pas être la OBJECT_ID() fonction ou une variable.

WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) )

S’applique à : SQL Server 2016 (13.x) et versions ultérieures.

Spécifie les partitions à tronquer ou à partir desquelles toutes les lignes sont supprimées. Si la table n’est pas partitionnée, l’argument WITH PARTITIONS génère une erreur. Si la WITH PARTITIONS clause n’est pas fournie, la table entière est tronquée.

<partition_number_expression> peut être spécifié des manières suivantes :

  • Spécifiez le numéro d’une partition, par exemple : WITH (PARTITIONS (2))

  • Spécifiez les numéros de plusieurs partitions individuelles séparés par des virgules, par exemple : WITH (PARTITIONS (1, 5))

  • Spécifiez à la fois des plages et des partitions individuelles, par exemple : WITH (PARTITIONS (2, 4, 6 TO 8))

  • <range> peut être spécifié en tant que numéros de partition séparés par le mot TO, par exemple : WITH (PARTITIONS (6 TO 8))

Pour tronquer une table partitionnée, la table et les index doivent être alignés (partitionnés sur la même fonction de partition).

Notes

Par rapport à l’instruction DELETE , TRUNCATE TABLE présente les avantages suivants :

  • Moindre espace du journal des transactions utilisé.

    L’instruction DELETE supprime les lignes une à la fois et enregistre une entrée dans le journal des transactions pour chaque ligne supprimée. TRUNCATE TABLE supprime les données en désallouant les pages de données utilisées pour stocker les données de la table et enregistre uniquement les désallocations de page dans le journal des transactions.

  • Moins de verrous utilisés.

    Lorsque l’instruction est exécutée à l’aide DELETE d’un verrou de ligne, chaque ligne de la table est verrouillée pour suppression. TRUNCATE TABLE verrouille toujours la table (y compris un verrou de schéma)SCH-Met une page, mais pas chaque ligne.

  • Sans exception, les pages zéros demeurent dans la table.

    Une fois qu’une DELETE instruction est exécutée, la table peut toujours contenir des pages vides. Par exemple, les pages vides d’un tas ne peuvent pas être libérées sans au moins un verrou de table exclusif (LCK_M_X). Si la suppression n'utilise pas de verrou de table, la table (le segment) contiendra beaucoup de pages vides. Pour les index, l’opération de suppression peut laisser les pages vides derrière elles, bien qu’un processus de nettoyage en arrière-plan désalloue rapidement ces pages.

TRUNCATE TABLE supprime toutes les lignes d’une table, mais la structure de la table et ses colonnes, contraintes, index, et ainsi de suite, restent. Pour supprimer la définition de la table en plus de ses données, utilisez l’instruction DROP TABLE.

Si la table contient une colonne d'identité, le compteur pour celle-ci est redéfini sur sa valeur de départ. Si aucune valeur initiale n’a été définie, la valeur 1 par défaut est utilisée. Pour conserver le compteur d’identité, utilisez DELETE plutôt.

Une TRUNCATE TABLE opération peut être restaurée dans une transaction.

Dans la base de données Fabric SQL, la troncation d’une table supprime toutes les données mises en miroir de Fabric OneLake pour cette table.

Limites

Vous ne pouvez pas utiliser TRUNCATE TABLE sur les tables qui :

  • Sont référencés par une FOREIGN KEY contrainte. Vous pouvez tronquer une table qui a une clé étrangère qui fait référence à elle-même.

  • participent à une vue indexée ;

  • sont publiées à l'aide d'une réplication transactionnelle ou de fusion.

  • sont temporelles avec versions gérées ;

  • Sont référencés par une EDGE contrainte.

Pour les tables avec une ou plusieurs de ces caractéristiques, utilisez plutôt l’instruction DELETE .

TRUNCATE TABLE ne peut pas activer un déclencheur, car l’opération ne journalise pas les suppressions de lignes individuelles. Pour plus d’informations, consultez CREATE TRIGGER (Transact-SQL).

Dans Azure Synapse Analytics et Analytics Platform System (PDW) :

  • TRUNCATE TABLE n’est pas autorisé dans l’instruction EXPLAIN .

  • TRUNCATE TABLE ne peut pas être exécuté à l’intérieur d’une transaction.

Tronquer de grandes tables

Microsoft SQL Server permet de supprimer ou de tronquer des tables dotées de plus de 128 extensions, sans maintenir de verrous simultanés sur toutes les extensions exigées pour la suppression.

Autorisations

L’autorisation minimale exigée est ALTER sur table_name. TRUNCATE TABLE les autorisations par défaut pour le propriétaire de la table, les membres du rôle serveur fixe sysadmin et les db_owner rôles de base de données fixes et db_ddladmin et ne sont pas transférables. Toutefois, vous pouvez incorporer l’instruction TRUNCATE TABLE dans un module, par exemple une procédure stockée, et accorder les autorisations appropriées au module à l’aide de la clause EXECUTE AS.

Exemples

R. Tronquer une table

L'exemple suivant supprime toutes les données de la table JobCandidate. Des instructions SELECT sont incluses avant et après l'instruction TRUNCATE TABLE pour comparer les résultats.

USE AdventureWorks2022;
GO

SELECT COUNT(*) AS BeforeTruncateCount
FROM HumanResources.JobCandidate;
GO

TRUNCATE TABLE HumanResources.JobCandidate;
GO

SELECT COUNT(*) AS AfterTruncateCount
FROM HumanResources.JobCandidate;
GO

B. Tronquer les partitions de table

S’applique à : SQL Server 2016 (13.x) et versions ultérieures.

L'exemple suivant tronque les partitions spécifiées d'une table partitionnée. La syntaxe WITH (PARTITIONS (2, 4, 6 TO 8)) provoque la troncation des numéros de partitions 2, 4, 6, 7 et 8.

TRUNCATE TABLE PartitionTable1
WITH (PARTITIONS (2, 4, 6 TO 8));
GO

C. Restaurer une opération de troncation

L’exemple suivant montre qu’une TRUNCATE TABLE opération à l’intérieur d’une transaction peut être restaurée.

  1. Créez une table de test avec trois lignes.

    USE [tempdb];
GO
CREATE TABLE TruncateTest (ID INT IDENTITY (1, 1) NOT NULL);
GO
INSERT INTO TruncateTest DEFAULT VALUES;
GO 3

  2. Vérifiez les données avant de tronquer.

    SELECT * FROM TruncateTest;
GO

  3. Tronquez la table dans une transaction et vérifiez le nombre de lignes.

    BEGIN TRANSACTION;

TRUNCATE TABLE TruncateTest;

SELECT * FROM TruncateTest;

    Vous voyez que la table est vide.

  4. Restaurez la transaction et vérifiez les données.

    ROLLBACK TRANSACTION;
GO

SELECT * FROM TruncateTest;
GO

    Vous voyez les trois lignes.

  5. Nettoyez la table.

    DROP TABLE TruncateTest;
GO

Contenu connexe