TRUNCATE TABLE (Transact-SQL)

Articolo
01/25/2023
5 minuti per la lettura

Si applica a: SQL Server Azure SQL Database Istanza gestita di SQL di Azure Azure Synapse Analytics Analytics Platform System (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 clausola WHERE. L'istruzione TRUNCATE TABLE è tuttavia più rapida e utilizza un numero minore di risorse di sistema e del log delle transazioni.

Convenzioni della sintassi Transact-SQL

Sintassi

-- Syntax for SQL Server and Azure 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 for Azure Synapse Analytics and 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 e versioni precedenti, vedere Documentazione delle versioni precedenti.

Argomenti

database_name

Nome del database.

schema_name

Nome dello schema a cui appartiene la tabella.

table_name

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

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

Si applica a: SQL Server ( SQL Server 2016 (13.x) fino alla versione corrente)

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

<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 sotto forma di 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 una riga 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 tramite un blocco di riga, ogni riga nella tabella viene bloccata per l'eliminazione. TRUNCATE TABLE blocca sempre la tabella e la pagina, incluso un blocco dello schema (SCH-M), ma non ogni riga.
Senza eccezione, le pagine di zeri vengono mantenute nella tabella.

Dopo l'esecuzione di una istruzione DELETE, la tabella può ancora 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ò tralasciare le pagine vuote, anche se queste pagine verranno deallocate velocemente da un processo di pulizia eseguito in background.

TRUNCATE TABLE rimuove tutte le righe da una tabella, ma non rimuove la struttura della tabella e le relative colonne, i vincoli, gli indici e così via. 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 utilizzato il valore predefinito. Per mantenere il contatore della tabella Identity, utilizzare l'istruzione DELETE.

Nota

È possibile eseguire il rollback di un'operazione TRUNCATE TABLE.

Restrizioni

Non è possibile usare TRUNCATE TABLE nelle tabelle:

a cui fa riferimento un vincolo FOREIGN KEY È 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.
a cui fa riferimento un vincolo EDGE.

Per le tabelle con una o più di queste caratteristiche, utilizzare l'istruzione DELETE come alternativa.

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

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

TRUNCATE TABLE non è consentita all'interno dell'istruzione EXPLAIN.
TRUNCATE TABLE non può essere eseguita all'interno di una transazione.

Troncamento delle 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. Le autorizzazioni TRUNCATE TABLE vengono assegnate per impostazione predefinita al proprietario della tabella, ai membri del ruolo predefinito del server sysadmin e ai membri dei ruoli predefiniti del database db_owner e db_ddladmin. Non sono trasferibili. È 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 AdventureWorks2012;  
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