Partager via

TABLEAU TRONQUÉ (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Entrepôt dans Microsoft FabricBase de données SQL 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

Syntax

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> | <gamme> } [ , ... 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).

Remarks

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 allouant les pages de données utilisées pour stocker la table et indexer les données 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, aucune page n’est laissée dans la table ou ses index.

    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 l’opération de suppression n’utilise pas de verrou de table, la table (tas) peut contenir de nombreuses pages vides. Pour les index, l’instruction DELETE peut laisser des pages vides derrière. Un processus de nettoyage en arrière-plan libère ensuite 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.

Allocation différée

Lorsqu’une table qui utilise 128 étendues ou plus est tronquée, le moteur de base de données reporte les désallocations de page réelles et leurs verrous associés, jusqu’à ce que la transaction soit validée. La troncation se produit en deux phases distinctes : logique et physique. Dans la phase logique, les unités d’allocation existantes utilisées par la table et ses index sont marquées pour la désallocation et verrouillées jusqu’à ce que la transaction valide. Dans la phase physique, un processus en arrière-plan supprime les pages marquées pour la désallocation. Cela signifie que l’espace libéré TRUNCATE TABLE par peut ne pas être disponible pour les nouvelles allocations immédiatement.

Si la récupération de base de données accélérée est activée, la troncation utilise des phases logiques et physiques distinctes, quel que soit le nombre d’étendues.

Limitations

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.

Permissions

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 sysadmin rôle serveur fixe et les db_ownerdb_ddladmin rôles de base de données fixes 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.

Examples

A. 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;

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

TRUNCATE TABLE HumanResources.JobCandidate;

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

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];

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 ID FROM TruncateTest;

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

    BEGIN TRANSACTION;

TRUNCATE TABLE TruncateTest;

SELECT ID FROM TruncateTest;

    Vous voyez que la table est vide.

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

    ROLLBACK TRANSACTION;

SELECT ID FROM TruncateTest;

    Vous voyez les trois lignes.

  5. Nettoyez la table.

    DROP TABLE TruncateTest;

Contenu connexe

  • Last updated on