risposta-alla-domanda-sullo-sviluppo-web-bd.com

Pro e contro di TRUNCATE vs DELETE FROM

Qualcuno potrebbe darmi una rapida panoramica dei pro e contro dell'uso delle seguenti due affermazioni:

TRUNCATE TABLE dbo.MyTable

vs

DELETE FROM dbo.MyTable

Sembra che entrambi facciano la stessa cosa quando tutto è detto e fatto; ma ci devono essere differenze tra i due.

59
Jim B

TRUNCATE non genera alcun dato di rollback, il che lo rende velocissimo. Semplicemente rilascia le pagine di dati usate dalla tabella.

Tuttavia, se si è in una transazione e si desidera la possibilità di "annullare" questa eliminazione, è necessario utilizzare DELETE FROM, che consente di eseguire il rollback.

EDIT: Si noti che quanto sopra non è corretto per SQL Server (ma si applica a Oracle). In SQL Server, è possibile eseguire il rollback di un'operazione troncata se ci si trova all'interno di una transazione e la transazione non è stata eseguita. Da una prospettiva di SQL Server, una differenza chiave tra DELETE FROM e TRUNCATE è this : "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 deallocating le pagine di dati utilizzate per memorizzare i dati della tabella e registra solo le deallocazioni della pagina nel log delle transazioni. "

In altre parole, durante il TRUNCATE viene registrato un numero di registrazioni minore poiché nel registro delle transazioni vengono registrate solo le deallocazioni della pagina, mentre con DELETE FROM ogni riga viene registrata l'eliminazione. Questo è uno dei motivi per cui TRUNCATE è fulmineo.

Si noti inoltre dal collegamento MSDN che non è possibile troncare le tabelle a cui fanno riferimento i vincoli di chiavi esterne, partecipare a una vista indicizzata o vengono pubblicate utilizzando la replica transazionale o la replica di tipo merge.

EDIT 2: Un altro punto chiave è che TRUNCATE TABLE resetterà la tua identità al seme iniziale, mentre DELETE FROM continuerà ad andare da dove era stato interrotto . Riferimento: risposta di Ben Robinson.

80
dcp

Un altro punto chiave non menzionato nelle altre risposte è che TRUNCATE TABLE sarà resetta la tua identità al seme iniziale , mentre DELETE FROM continuerà ad aumentare da dove era stato interrotto.

46
Ben Robinson

Un'altra differenza dal punto di vista della sicurezza è che TRUNCATE richiede i privilegi ALTER sulla tabella, mentre DELETE richiede semplicemente (rullo di tamburi) autorizzazioni DELETE su quella tabella. 

8
Kyle Hale

TRUNCATE TABLE non registra la transazione. Ciò significa che è un fulmine veloce per i tavoli di grandi dimensioni. Lo svantaggio è che non è possibile annullare l'operazione.

DELETE FROM registra ogni riga che viene cancellata nei registri delle transazioni, quindi l'operazione richiede un po 'di tempo e fa aumentare drasticamente i log delle transazioni. Il vantaggio è che è possibile annullare l'operazione se necessario.

4
Justin Niessner

Struttura di Elimina V Troncare nel server SQL 

Per l'articolo completo prendere dopo questa connessione: Elimina Vs Truncate in SQL Server

 enter image description here

/*Truncate - Syntax*/
TRUNCATE TABLE table_name

/*Delete - Syntax*/
DELETE FROM table_name
WHERE some_condition
3
Rahul Modi

Credo che Delete and Truncate possa essere ripristinato solo se l'operazione è stata eseguita in e transazione esplicita. Altrimenti dovresti eseguire un ripristino per recuperare i dati rimossi

2
CNkatcher

La differenza fondamentale è nel modo in cui sono registrati. DELETE e TRUNCATE vengono registrati in modo diverso, ma entrambi possono essere ripristinati esattamente allo stesso modo. Tutte le operazioni che modificano i dati vengono registrate. In SQL Server non esiste un'operazione non registrata.

1
nvogel

Inoltre tutte le risposte, un altro punto da considerare che Truncate non innescherà il delete trigger della tabella, ma l'istruzione delete attiverà il delete trigger della tabella per ogni riga.

0
Melad Ezzat

Una cosa che è molto importante (imo) e non menzionata in altre risposte è che TRUNCATE ha bisogno del blocco della stabilità dello schema, Sch-S, mentre DELETE utilizza i blocchi di riga. Consente di ispezionare quanto segue:

BEGIN TRANSACTION;

BEGIN TRY
    -- Truncate below will take LCK_M_SCH_S lock for TABLE_A
    TRUNCATE TABLE TABLE_A

    -- Lets say the query below takes 5 hours to execute
    INSERT INTO
        TABLE_A
    SELECT
        *
    FROM
        GIANT_TABLE (NOLOCK)
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    THROW
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

Supponiamo ora che dopo 1-2 minuti dall'inizio di questa query, diciamo che abbiamo provato ad eseguire quanto segue:

SELECT COUNT(*) FROM TABLE_A (NOLOCK)

Si noti che ho usato la clausola NOLOCK. Cosa pensi che succederà ora? Questa query attenderà 5 ore. Perché? Poiché la clausola NOLOCK richiede Sch-S lock su TABLE_A ma quella TRUNCATE ha già Sch-S su di essa. Poiché non abbiamo ancora eseguito il commit della transazione, il blocco è ancora attivo anche dopo la clausola TRUNCATE. Sch-S lock su una tabella significa fondamentalmente che TABLE_A viene modificato aggiungendo/rimuovendo colonne ecc. o viene troncato. Non puoi nemmeno eseguire qualcosa come qui sotto:

SELECT object_id('TABLE_A')

Questo si bloccherà anche per 5 ore. Tuttavia, se sostituisci TRUNCATE con DELETE FROM, vedrai che non ci sarà alcun lock Sch-S sulla tabella e le query di cui sopra non verranno bloccate.

0
sotn

Un'altra differenza tra DELETE vs TRUNCATE è il comportamento quando la tabella è corrotta.

Per esempio:

DELETE FROM table_name;

Finirà con l'errore:

Messaggio 3314, livello 21, stato 3, riga 1

Durante l'annullamento di un'operazione registrata nel database '...', si è verificato un errore nel registro ID record (). In genere, l'errore specifico viene registrato in precedenza come un errore nel servizio Registro eventi di Windows. Ripristinare il database o il file da un backup o riparare il database.

Messaggio 0, livello 20, stato 0, riga 0

Si è verificato un grave errore nel comando corrente. I risultati, se presenti, dovrebbero essere scartati.

Mentre TRUNCATE funzionerà:

TRUNCATE TABLE table_name;
-- Command(s) completed successfully.
0
Lukasz Szozda