TRUNCATE TABLE (Transact-SQL)

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di strumenti analitici (PDW)

Rimuove tutte le righe da una tabella o da partizioni specificate di una tabella senza registrare le eliminazioni delle singole righe. TRUNCATE TABLE è simile all'istruzione DELETE senza WHERE clausola. Tuttavia, TRUNCATE TABLE è più veloce e usa meno risorse di sistema e log delle transazioni.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi per SQL Server e database SQL di Azure.

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>

Sintassi per Azure Synapse Analytics e Parallel Data Warehouse.

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

Nota

Per visualizzare la sintassi Transact-SQL per SQL Server 2014 (12.x) e versioni precedenti, vedere la documentazione delle versioni precedenti.

Argomenti

database_name

Nome del database.

schema_name

Nome del processo a cui appartiene la tabella.

table_name

Nome della tabella da troncare o da cui rimuovere tutte le righe. table_name deve essere un valore letterale. table_name non può essere la OBJECT_ID() funzione o una variabile.

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

Si applica a: SQL Server 2016 (13.x) e versioni successive.

Specifica le partizioni da troncare o da cui vengono rimosse tutte le righe. Se la tabella non è partizionata, l'argomento WITH PARTITIONS genera un errore. Se la WITH PARTITIONS clausola non viene specificata, l'intera tabella viene troncata.

<partition_number_expression> può essere specificato nei modi seguenti:

  • Specificare il numero di una partizione, ad esempio: WITH (PARTITIONS (2))

  • Specificare i numeri di partizione per più partizioni singole separati da virgole, ad esempio: WITH (PARTITIONS (1, 5))

  • Specificare sia intervalli sia singole partizioni, ad esempio: WITH (PARTITIONS (2, 4, 6 TO 8))

  • <range> può essere specificato come numeri di partizione separati dalla parola TO, ad esempio: WITH (PARTITIONS (6 TO 8))

Per troncare una tabella partizionata, la tabella e gli indici devono essere allineati (partizionati nella stessa funzione di partizione).

Osservazioni:

Rispetto all'istruzione DELETE , TRUNCATE TABLE presenta i vantaggi seguenti:

  • Richiede una minore quantità di spazio del log delle transazioni.

    L'istruzione DELETE rimuove le righe una alla volta e registra una voce nel log delle transazioni per ogni riga eliminata. L'istruzione TRUNCATE TABLE rimuove i dati tramite la deallocazione delle pagine di dati usate per archiviare i dati della tabella. Solo le deallocazioni di pagina vengono registrate nel log delle transazioni.

  • In genere utilizza meno blocchi.

    Quando l'istruzione DELETE viene eseguita usando un blocco di riga, ogni riga della tabella viene bloccata per l'eliminazione. TRUNCATE TABLE blocca sempre la tabella (incluso un blocco dello schema (SCH-M) e la pagina, ma non ogni riga.

  • Senza eccezione, le pagine di zeri vengono mantenute nella tabella.

    Dopo l'esecuzione di un'istruzione DELETE , la tabella può comunque contenere pagine vuote. Ad esempio, le pagine vuote in un heap non possono essere deallocate senza almeno un blocco di tabella esclusivo (LCK_M_X). Se l'operazione di eliminazione non utilizza un blocco di tabella, la tabella (heap) conterrà molte pagine vuote. Per gli indici, l'operazione di eliminazione può lasciare le pagine vuote, anche se un processo di pulizia in background dealloca rapidamente queste pagine.

TRUNCATE TABLE rimuove tutte le righe da una tabella, ma la struttura della tabella e le relative colonne, vincoli, indici e così via rimangono. Per rimuovere la definizione della tabella in aggiunta ai relativi dati, usare l'istruzione DROP TABLE.

Se la tabella include una colonna Identity, il contattore per quella colonna viene reimpostato sul valore di inizializzazione definito per la colonna. Se non è stato definito alcun valore di inizializzazione, viene usato il valore 1 predefinito. Per mantenere il contatore delle identità, usare DELETE invece .

È possibile eseguire il rollback di un'operazione TRUNCATE TABLE all'interno di una transazione.

Limiti

Non è possibile usare TRUNCATE TABLE nelle tabelle che:

  • Viene fatto riferimento a un FOREIGN KEY vincolo. È possibile troncare una tabella con una chiave esterna che fa riferimento alla tabella stessa.

  • che partecipano in una vista indicizzata

  • che sono pubblicate tramite una replica transazionale o una replica di tipo merge.

  • che sono tabelle temporali con controllo delle versioni di sistema.

  • Viene fatto riferimento a un EDGE vincolo.

Per le tabelle con una o più di queste caratteristiche, usare invece l'istruzione DELETE .

TRUNCATE TABLE non può attivare un trigger perché l'operazione non registra le eliminazioni di singole righe. Per altre informazioni, vedere CREATE TRIGGER (Transact-SQL).

In Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW):

  • TRUNCATE TABLE non è consentito all'interno dell'istruzione EXPLAIN .

  • TRUNCATE TABLE non può essere eseguito all'interno di una transazione.

Troncare tabelle di grandi dimensioni

In Microsoft SQL Server è possibile eliminare o troncare le tabelle che includono più di 128 extent senza mantenere attivi blocchi simultanei in tutti gli extent necessari per l'eliminazione.

Autorizzazioni

L'autorizzazione minima necessaria è ALTER per table_name. TRUNCATE TABLEle autorizzazioni predefinite sono il proprietario della tabella, i membri del ruolo predefinito del server sysadmin e i db_owner ruoli predefiniti del database e db_ddladmin e non sono trasferiscibili. È comunque possibile incorporare l'istruzione TRUNCATE TABLE all'interno di un modulo, ad esempio una stored procedure, e concedere le autorizzazioni necessarie al modulo tramite la clausola EXECUTE AS.

Esempi

R. Troncare una tabella

Nell'esempio seguente vengono rimossi tutti i dati dalla tabella JobCandidate. Le istruzioni SELECT vengono inserite prima e dopo l'istruzione TRUNCATE TABLE per confrontare i risultati.

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. Troncare le partizioni di una tabella

Si applica a: SQL Server 2016 (13.x) e versioni successive.

L'esempio seguente tronca le partizioni specificate di una tabella partizionata. La sintassi WITH (PARTITIONS (2, 4, 6 TO 8)) consente di troncare i numeri di partizione 2, 4, 6, 7 e 8.

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

C. Eseguire il rollback di un'operazione di troncamento

Nell'esempio seguente viene illustrato che è possibile eseguire il rollback di un'operazione TRUNCATE TABLE all'interno di una transazione.

  1. Creare una tabella di test con tre righe.

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

  2. Controllare i dati prima del troncamento.

    SELECT * FROM TruncateTest;
GO

  3. Troncare la tabella all'interno di una transazione e controllare il numero di righe.

    BEGIN TRANSACTION;

TRUNCATE TABLE TruncateTest;

SELECT * FROM TruncateTest;

    Si noterà che la tabella è vuota.

  4. Eseguire il rollback della transazione e controllare i dati.

    ROLLBACK TRANSACTION;
GO

SELECT * FROM TruncateTest;
GO

    Vengono visualizzate tutte e tre le righe.

  5. Pulire la tabella.

    DROP TABLE TruncateTest;
GO

Contenuto correlato