Delen via

TRUNCATE TABLE (Transact-SQL)

  • Artikel

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Warehouse in Microsoft FabricSQL-database in Microsoft Fabric

Verwijdert alle rijen uit een tabel of opgegeven partities van een tabel, zonder de afzonderlijke rijverwijderingen te registreren. TRUNCATE TABLE is vergelijkbaar met de DELETE-instructie zonder WHERE component; TRUNCATE TABLE is echter sneller en maakt gebruik van minder systeem- en transactielogboekbronnen.

Transact-SQL syntaxisconventies

Syntaxis

Syntaxis voor 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>

Syntaxis voor Microsoft Fabric, Azure Synapse Analytics en Parallel Data Warehouse.

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

Argumenten

database_name

De naam van de database.

schema_name

De naam van het schema waartoe de tabel behoort.

table_name

De naam van de tabel die moet worden afgekapt of waaruit alle rijen worden verwijderd. table_name moet een letterlijke naam zijn. table_name kan de OBJECT_ID() functie of een variabele niet zijn.

WITH ( PARTITIES ( { <partition_number_expression> | <bereik> } [ , ... n ] ) )

Van toepassing op: SQL Server 2016 (13.x) en latere versies.

Hiermee geeft u de partities afkappen of waaruit alle rijen worden verwijderd. Als de tabel niet is gepartitioneerd, genereert het argument WITH PARTITIONS een fout. Als de WITH PARTITIONS component niet is opgegeven, wordt de hele tabel afgekapt.

<partition_number_expression> kunt u op de volgende manieren opgeven:

  • Geef het aantal partities op, bijvoorbeeld: WITH (PARTITIONS (2))

  • Geef de partitienummers op voor verschillende afzonderlijke partities, gescheiden door komma's, bijvoorbeeld: WITH (PARTITIONS (1, 5))

  • Geef zowel bereiken als afzonderlijke partities op, bijvoorbeeld: WITH (PARTITIONS (2, 4, 6 TO 8))

  • <range> kan worden opgegeven als partitienummers gescheiden door het woord TO, bijvoorbeeld: WITH (PARTITIONS (6 TO 8))

Als u een gepartitioneerde tabel wilt afkappen, moeten de tabel en indexen worden uitgelijnd (gepartitioneerd op dezelfde partitiefunctie).

Opmerkingen

In vergelijking met de DELETE-instructie heeft TRUNCATE TABLE de volgende voordelen:

  • Er wordt minder transactielogboekruimte gebruikt.

    De instructie DELETE verwijdert rijen één voor één en registreert een vermelding in het transactielogboek voor elke verwijderde rij. TRUNCATE TABLE verwijdert de gegevens door de gegevenspagina's die worden gebruikt om de tabelgegevens op te slaan en alleen de pagina-deallocaties in het transactielogboek te registreren.

  • Er worden doorgaans minder vergrendelingen gebruikt.

    Wanneer de instructie DELETE wordt uitgevoerd met behulp van een rijvergrendeling, wordt elke rij in de tabel vergrendeld voor verwijdering. TRUNCATE TABLE vergrendelt altijd de tabel (inclusief een schemavergrendeling (SCH-M) en pagina, maar niet elke rij.

  • Zonder uitzondering worden er nul pagina's in de tabel achtergelaten.

    Nadat een DELETE instructie is uitgevoerd, kan de tabel nog steeds lege pagina's bevatten. Lege pagina's in een heap kunnen bijvoorbeeld niet ongedaan worden gemaakt zonder ten minste een exclusieve (LCK_M_X) tabelvergrendeling. Als de verwijderbewerking geen tabelvergrendeling gebruikt, bevat de tabel (heap) veel lege pagina's. Voor indexen kan de verwijderbewerking lege pagina's achter laten, hoewel de toewijzing van deze pagina's snel ongedaan wordt gemaakt door een opschoonproces op de achtergrond.

TRUNCATE TABLE verwijdert alle rijen uit een tabel, maar de tabelstructuur en de bijbehorende kolommen, beperkingen, indexen, enzovoort, blijven behouden. Als u de tabeldefinitie naast de gegevens wilt verwijderen, gebruikt u de DROP TABLE-instructie.

Als de tabel een identiteitskolom bevat, wordt de teller voor die kolom opnieuw ingesteld op de seed-waarde die voor de kolom is gedefinieerd. Als er geen seed is gedefinieerd, wordt de standaardwaarde 1 gebruikt. Als u het identiteitsitem wilt behouden, gebruikt u in plaats daarvan DELETE.

Een TRUNCATE TABLE bewerking kan worden teruggedraaid binnen een transactie.

Als u in Fabric SQL Database een tabel afkapt, worden alle gespiegelde gegevens uit Fabric OneLake voor die tabel verwijderd.

Beperkingen

U kunt TRUNCATE TABLE niet gebruiken voor tabellen die:

  • Er wordt naar een FOREIGN KEY beperking verwezen. U kunt een tabel afkappen met een refererende sleutel die naar zichzelf verwijst.

  • Deelnemen aan een geïndexeerde weergave.

  • Worden gepubliceerd met behulp van transactionele replicatie of samenvoegingsreplicatie.

  • Zijn tijdelijke systeemversies.

  • Er wordt naar een EDGE beperking verwezen.

Gebruik voor tabellen met een of meer van deze kenmerken in plaats daarvan de instructie DELETE.

TRUNCATE TABLE kan een trigger niet activeren omdat met de bewerking geen afzonderlijke rijverwijderingen worden vastgelegd. Zie CREATE TRIGGER (Transact-SQL)voor meer informatie.

In Azure Synapse Analytics and Analytics Platform System (PDW):

  • TRUNCATE TABLE is niet toegestaan binnen de instructie EXPLAIN.

  • TRUNCATE TABLE kan niet worden uitgevoerd binnen een transactie.

Grote tabellen afkappen

Microsoft SQL Server biedt de mogelijkheid om tabellen met meer dan 128 gebieden te verwijderen of af tekappen zonder gelijktijdige vergrendelingen vast te houden voor alle vereiste gebieden voor de afname.

Machtigingen

De minimale vereiste machtiging is ALTER op table_name. TRUNCATE TABLE machtigingen zijn standaard ingesteld op de eigenaar van de tabel, leden van de vaste serverfunctie sysadmin en de db_owner en db_ddladmin vaste databaserollen, en zijn niet overdraagbaar. U kunt de instructie TRUNCATE TABLE echter opnemen in een module, zoals een opgeslagen procedure, en de juiste machtigingen verlenen aan de module met behulp van de EXECUTE AS-component.

Voorbeelden

Een. Een tabel afkappen

In het volgende voorbeeld worden alle gegevens uit de JobCandidate tabel verwijderd. SELECT instructies worden vóór en na de TRUNCATE TABLE instructie opgenomen om de resultaten te vergelijken.

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. Tabelpartities afkappen

Van toepassing op: SQL Server 2016 (13.x) en latere versies.

In het volgende voorbeeld worden opgegeven partities van een gepartitioneerde tabel afgekapt. De WITH (PARTITIONS (2, 4, 6 TO 8)) syntaxis zorgt ervoor dat partitienummers 2, 4, 6, 7 en 8 worden afgekapt.

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

C. Een afkappende bewerking terugdraaien

In het volgende voorbeeld ziet u dat een TRUNCATE TABLE bewerking binnen een transactie kan worden teruggedraaid.

  1. Maak een testtabel met drie rijen.

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

  2. Controleer de gegevens voordat u afkappen.

    SELECT * FROM TruncateTest;
GO

  3. Kap de tabel in een transactie af en controleer het aantal rijen.

    BEGIN TRANSACTION;

TRUNCATE TABLE TruncateTest;

SELECT * FROM TruncateTest;

    U ziet dat de tabel leeg is.

  4. De transactie terugdraaien en de gegevens controleren.

    ROLLBACK TRANSACTION;
GO

SELECT * FROM TruncateTest;
GO

    U ziet alle drie de rijen.

  5. Schoon de tabel op.

    DROP TABLE TruncateTest;
GO

Verwante inhoud