Condividi tramite

TRUNCATE TABLE (Transact-SQL)

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di analisi (PDW)Warehouse in Microsoft FabricDatabase SQL in Anteprima di Microsoft Fabric

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

Syntax

Sintassi per SQL Server, database SQL di Azure, database SQL dell'infrastruttura

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 Microsoft Fabric, Azure Synapse Analytics e Parallel Data Warehouse.

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

Arguments

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

Remarks

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. TRUNCATE TABLE rimuove i dati deallocando le pagine di dati utilizzate per archiviare i dati della tabella e dell'indice e registra solo le deallocazione delle pagine 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, nessuna pagina viene lasciata nella tabella o nei relativi indici.

    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 usa un blocco di tabella, la tabella (heap) potrebbe contenere molte pagine vuote. Per gli indici, l'istruzione DELETE può lasciare le pagine vuote. Un processo di pulizia in background dealloca quindi 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.

Nel database SQL di Infrastruttura il troncamento di una tabella elimina tutti i dati con mirroring da Fabric OneLake per tale tabella.

Deferred deallocation

Quando una tabella che usa 128 extent o più è troncata, il motore di database rinvia le deallocazione di pagina effettive e i relativi blocchi associati fino a quando la transazione viene sottoposta a commit. Il troncamento si verifica in due fasi separate: logica e fisica. Nella fase logica, le unità di allocazione esistenti usate dalla tabella e i relativi indici vengono contrassegnate per la deallocazione e bloccate fino al commit della transazione. Nella fase fisica, un processo in background rimuove le pagine contrassegnate per la deallocazione. Ciò significa che lo spazio rilasciato da TRUNCATE TABLE potrebbe non essere disponibile immediatamente per le nuove allocazioni.

Se il ripristino accelerato del database è abilitato, il troncamento usa fasi logiche e fisiche separate indipendentemente dal numero di extent.

Limitations

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.

Permissions

L'autorizzazione minima necessaria è ALTER per table_name. TRUNCATE TABLE le autorizzazioni predefinite sono il proprietario della tabella, i membri del ruolo predefinito del sysadmin server e i db_owner ruoli predefiniti del database e db_ddladmin 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.

Examples

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

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

TRUNCATE TABLE HumanResources.JobCandidate;

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

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

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

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

    BEGIN TRANSACTION;

TRUNCATE TABLE TruncateTest;

SELECT ID FROM TruncateTest;

    Si noterà che la tabella è vuota.

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

    ROLLBACK TRANSACTION;

SELECT ID FROM TruncateTest;

    Vengono visualizzate tutte e tre le righe.

  5. Pulire la tabella.

    DROP TABLE TruncateTest;

Related content