Pros & Cons of TRUNCATE vs DELETE FROM

问题:

Could someone give me a quick overview of the pros and cons of using the following two statements:

TRUNCATE TABLE dbo.MyTable

vs

DELETE FROM dbo.MyTable

It seems like they both do the same thing when all is said and done; but are there must be differences between the two.

回答1:

TRUNCATE doesn't generate any rollback data, which makes it lightning fast. It just deallocates the data pages used by the table.

However, if you are in a transaction and want the ability to "undo" this delete, you need to use DELETE FROM, which gives the ability to rollback.

EDIT: Note that the above is incorrect for SQL Server (but it does apply to Oracle). In SQL Server, it is possible to rollback a truncate operation if you are inside a transaction and the transaction has not been committed. From a SQL Server perspective, one key difference between DELETE FROM and TRUNCATE is this: "The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log."

In other words, there is less logging during a TRUNCATE because only the page deallocations are recorded in the transaction log, whereas with a DELETE FROM each row deletion is recorded. That's one of the reasons TRUNCATE is lightning fast.

Note also from that MSDN link that you cannot truncate tables that are referenced by foreign key constraints, participate in an indexed view, or are published by using transactional replication or merge replication.

EDIT 2: Another key point is that TRUNCATE TABLE will reset your identity to the initial seed, whereas DELETE FROM will carry on incrementing from where it left off. Reference: Ben Robinson's answer.

回答2:

Another key point not mentioned in the other answers is that TRUNCATE TABLE will reset your identity to the initial seed, whereas DELETE FROM will carry on incrementing from where it left off.

回答3:

Another difference from a security perspective is that TRUNCATE requires ALTER privileges on the table, while DELETE merely requires (drum roll) DELETE permissions on that table.

回答4:

TRUNCATE TABLE doesn't log the transaction. That means it is lightning fast for large tables. The downside is that you can't undo the operation.

DELETE FROM logs each row that is being deleted in the transaction logs so the operation takes a while and causes your transaction logs to grow dramatically. The upside is that you can undo the operation if need be.

回答5:

I believe Delete and Truncate can only be rolled back if the operation was executed in and explicit transaction. Otherwise you would have to perform a restore to recover the removed data

回答6:

Outline of Delete Vs Truncate in SQL server

For Complete Article take after this connection: Delete Vs Truncate in SQL Server

/*Truncate - Syntax*/
TRUNCATE TABLE table_name

/*Delete - Syntax*/
DELETE FROM table_name
WHERE some_condition

回答7:

The fundamental difference is in the way they are logged. DELETE and TRUNCATE are logged differently but both can be rolled back in exactly the same way. All operations that change data are logged. In SQL Server there is no such thing as a non-logged operation.

回答8:

One thing that's very important(imo) and not mentioned on other answers is that TRUNCATE needs Schema Stability lock, Sch-S, whereas DELETE uses row locks. Lets inspect the following:

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;

Now assume that after 1-2 minutes of the start of this query, let's say we tried to execute the following:

SELECT COUNT(*) FROM TABLE_A (NOLOCK)

Notice that I used NOLOCK clause. What do you think will happen now? This query will wait 5 hours. Why? Because NOLOCK clause needs Sch-S lock on TABLE_A but that TRUNCATE clause has Sch-S on it already. Since we didn't commit the transaction yet, the lock is still on even after that TRUNCATE clause. Sch-S lock on a table basically means that either TABLE_A is being altered by adding/removing columns etc. or it's being truncated. You even can't execute something like below:

SELECT object_id('TABLE_A')

This will stuck 5 hours too. However, if you replace that TRUNCATE with DELETE FROM, you'll see that there will be no Sch-S lock on the table and the queries above will not get stucked.

回答9:

Another difference between DELETE vs TRUNCATE is behaviour when table is corrupted.

For instance:

DELETE FROM table_name;

Will end up with error:

Msg 3314, Level 21, State 3, Line 1

During undoing of a logged operation in database '...', an error occurred at log record ID (). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

While TRUNCATE will work:

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

回答10:

Plus all the answers, another point to consider that Truncate will not trigger the delete trigger of the table, but delete statement will trigger the delete trigger of the table for each row.

回答11:

$connection = $this->getEntityManager()->getConnection();
$connection->exec("Truncate TABLE <tablename>;");

回答12:

truncate doesnt do any logging, delete does, so if you have a ton of records, your trans log is huge