Dela via

TRUNKERA TABELL (Transact-SQL)

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Warehouse i Microsoft FabricSQL-databas i Microsoft Fabric

Tar bort alla rader från en tabell eller angivna partitioner i en tabell, utan att logga de enskilda radborttagningarna. TRUNCATE TABLE liknar DELETE-instruktionen utan WHERE-sats. men TRUNCATE TABLE är snabbare och använder färre system- och transaktionsloggresurser.

Transact-SQL syntaxkonventioner

Syntax

Syntax för 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>

Syntax för Microsoft Fabric, Azure Synapse Analytics och Parallel Data Warehouse.

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

Argument

database_name

Namnet på databasen.

schema_name

Namnet på schemat som tabellen tillhör.

table_name

Namnet på tabellen som ska trunkeras eller från vilken alla rader tas bort. table_name måste vara en literal. table_name kan inte vara funktionen OBJECT_ID() eller en variabel.

WITH ( PARTITIONER ( { <partition_number_expression> | <intervall> } [ , ... n ] ) )

gäller för: SQL Server 2016 (13.x) och senare versioner.

Anger de partitioner som ska trunkeras eller från vilka alla rader tas bort. Om tabellen inte är partitionerad genererar argumentet WITH PARTITIONS ett fel. Om WITH PARTITIONS-satsen inte tillhandahålls trunkeras hela tabellen.

<partition_number_expression> kan anges på följande sätt:

  • Ange antalet partitioner, till exempel: WITH (PARTITIONS (2))

  • Ange partitionsnumren för flera enskilda partitioner avgränsade med kommatecken, till exempel: WITH (PARTITIONS (1, 5))

  • Ange både intervall och enskilda partitioner, till exempel: WITH (PARTITIONS (2, 4, 6 TO 8))

  • <range> kan anges som partitionsnummer avgränsade med ordet TO, till exempel: WITH (PARTITIONS (6 TO 8))

Om du vill trunkera en partitionerad tabell måste tabellen och indexen vara justerade (partitionerade på samma partitionsfunktion).

Anmärkningar

Jämfört med DELETE-instruktionen har TRUNCATE TABLE följande fördelar:

  • Mindre transaktionsloggutrymme används.

    Instruktionen DELETE tar bort rader en i taget och registrerar en post i transaktionsloggen för varje borttagen rad. TRUNCATE TABLE tar bort data genom att frigöra de datasidor som används för att lagra tabelldata och endast registrerar sidborttagningarna i transaktionsloggen.

  • Färre lås används vanligtvis.

    När DELETE-instruktionen körs med hjälp av ett radlås låses varje rad i tabellen för borttagning. TRUNCATE TABLE låser alltid tabellen (inklusive ett schema (SCH-M) lås) och sida, men inte varje rad.

  • Utan undantag finns inga sidor kvar i tabellen.

    När en DELETE-instruktion har körts kan tabellen fortfarande innehålla tomma sidor. Tomma sidor i en heap kan till exempel inte frigöras utan minst ett exklusivt tabelllås (LCK_M_X). Om borttagningsåtgärden inte använder ett tabelllås innehåller tabellen (heap) många tomma sidor. För index kan borttagningsåtgärden lämna tomma sidor kvar, även om en bakgrundsrensningsprocess frigör dessa sidor snabbt.

TRUNCATE TABLE tar bort alla rader från en tabell, men tabellstrukturen och dess kolumner, begränsningar, index och så vidare finns kvar. Om du vill ta bort tabelldefinitionen utöver dess data använder du instruktionen DROP TABLE.

Om tabellen innehåller en identitetskolumn återställs räknaren för den kolumnen till det startvärde som definierats för kolumnen. Om inget frö har definierats används standardvärdet 1. Om du vill behålla identitetsräknaren använder du DELETE i stället.

En TRUNCATE TABLE åtgärd kan återställas inom en transaktion.

Om du trunkerar en tabell i Fabric SQL-databasen tas alla speglade data bort från Fabric OneLake för den tabellen.

Begränsningar

Du kan inte använda TRUNCATE TABLE i tabeller som:

  • Refereras till av en FOREIGN KEY begränsning. Du kan trunkera en tabell som har en sekundärnyckel som refererar till sig själv.

  • Delta i en indexerad vy.

  • Publiceras med hjälp av transaktionsreplikering eller sammanslagningsreplikering.

  • Är systemversionsbaserade temporala.

  • Refereras till av en EDGE begränsning.

För tabeller med en eller flera av dessa egenskaper använder du instruktionen DELETE i stället.

TRUNCATE TABLE kan inte aktivera en utlösare eftersom åtgärden inte loggar enskilda radborttagningar. Mer information finns i CREATE TRIGGER (Transact-SQL).

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

  • TRUNCATE TABLE tillåts inte i EXPLAIN-instruktionen.

  • TRUNCATE TABLE kan inte köras i en transaktion.

Trunkera stora tabeller

Microsoft SQL Server har möjlighet att släppa eller trunkera tabeller som har mer än 128 omfattningar utan att hålla samtidiga lås i alla de utsträckningar som krävs för släppet.

Behörigheter

Den minsta behörighet som krävs är ALTER på table_name. TRUNCATE TABLE behörigheter som standard för tabellägaren, medlemmar i den fasta serverrollen sysadmin och db_owner och db_ddladmin fasta databasroller och kan inte överföras. Du kan dock införliva TRUNCATE TABLE-instruktionen i en modul, till exempel en lagrad procedur, och bevilja lämplig behörighet till modulen med hjälp av EXECUTE AS-satsen.

Exempel

A. Trunkera en tabell

Följande exempel tar bort alla data från tabellen JobCandidate. SELECT-instruktioner inkluderas före och efter TRUNCATE TABLE-instruktionen för att jämföra resultat.

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. Trunkera tabellpartitioner

gäller för: SQL Server 2016 (13.x) och senare versioner.

I följande exempel trunkeras angivna partitioner i en partitionerad tabell. Syntaxen WITH (PARTITIONS (2, 4, 6 TO 8)) gör att partitionsnummer 2, 4, 6, 7 och 8 trunkeras.

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

C. Återställa en trunkeringsåtgärd

I följande exempel visas att en TRUNCATE TABLE åtgärd i en transaktion kan återställas.

  1. Skapa en testtabell med tre rader.

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

  2. Kontrollera data innan du trunkerar.

    SELECT * FROM TruncateTest;
GO

  3. Trunkera tabellen i en transaktion och kontrollera antalet rader.

    BEGIN TRANSACTION;

TRUNCATE TABLE TruncateTest;

SELECT * FROM TruncateTest;

    Du ser att tabellen är tom.

  4. Återställ transaktionen och kontrollera data.

    ROLLBACK TRANSACTION;
GO

SELECT * FROM TruncateTest;
GO

    Du ser alla tre raderna.

  5. Rensa tabellen.

    DROP TABLE TruncateTest;
GO

Relaterat innehåll