截断与删除的优缺点

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

谁能给我简单介绍一下使用以下两种说法的利弊?

TRUNCATE TABLE dbo.MyTable

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.

当所有的事情都说了又做了，他们似乎都做了同样的事情;但两者之间一定有区别吗?

11 个解决方案

#1

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

TRUNCATE不生成任何回滚数据，这使得它非常快速。它只是分配表使用的数据页。

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.

但是，如果您在事务中，并且希望能够“撤消”此删除操作，则需要使用delete FROM，这将提供回滚功能。

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."

编辑:注意，上面的语句对SQL Server不正确(但它确实适用于Oracle)。在SQL Server中，如果您在事务中，并且没有提交事务，则可以回滚截断操作。从SQL Server的角度来看，DELETE From和truncatetable之间的一个关键区别是:“DELETE语句每次删除一行，并为每删除一行记录事务日志中的一个条目。”truncatetable可以通过释放用于存储表数据的数据页来删除数据，并只记录事务日志中的页面释放。

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:另一个关键点是TRUNCATE表将重置您的身份到初始种子，而删除将继续增加，从它停止的地方。参考:本·罗宾逊的回答。

#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.

其他答案中没有提到的另一个关键点是TRUNCATE表将重置您的身份到初始种子，而DELETE将继续从它停止的地方进行递增。

#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.

从安全性角度来看，另一个不同之处是，TRUNCATE需要在表上修改特权，而DELETE仅仅需要(drum roll)删除该表上的权限。

#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.

truncatetable不记录事务。这意味着对于大桌子来说，它的速度非常快。缺点是不能撤消操作。

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

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.

根本的区别在于记录它们的方式。删除和截断记录的方式不同，但可以以完全相同的方式回滚。记录所有更改数据的操作。在SQL Server中，不存在未记录的操作。

#7

Outline of Delete Vs Truncate in SQL server

SQL server中删除与截断的轮廓

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

对于完整的文章，在此连接之后:在SQL Server中删除Vs truncatetable

/*Truncate - Syntax*/
TRUNCATE TABLE table_name

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

#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:

有一件事非常重要(在我看来)，但在其他答案中没有提到，那就是truncatetable需要Schema Stability lock, su - s，而DELETE使用行锁。让我们检查以下几点:

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:

现在假设查询开始1-2分钟后，我们尝试执行以下操作:

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:

注意，我使用了NOLOCK子句。你认为现在会发生什么?此查询将等待5小时。为什么?因为NOLOCK子句需要在TABLE_A上使用su - s锁，但是这个truncatetable子句已经有了su - s锁。因为我们还没有提交事务，所以即使在截断子句之后，锁仍然是打开的。表上的su - s锁基本上意味着要么通过添加/删除列等方式修改TABLE_A，要么被截断。你甚至不能执行如下内容:

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.

这也需要5个小时。但是，如果您用DELETE FROM替换这个截断，您将看到表上不会有ssl锁，并且上面的查询不会被阻塞。

#9

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

DELETE和TRUNCATE之间的另一个区别是当表损坏时的行为。

For instance:

例如:

DELETE FROM table_name;

Will end up with error:

将以错误结束:

Msg 3314, Level 21, State 3, Line 1

Msg 3314, 21层，状态3，线路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.

在删除数据库中的日志操作时……，日志记录ID()出现错误。通常，特定的失败在Windows事件日志服务中被记录为错误。从备份中恢复数据库或文件，或修复数据库。

Msg 0, Level 20, State 0, Line 0

Msg 0, 20级，状态0，行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

-1

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

#11

-2

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

truncate不做任何日志记录，delete做，所以如果你有大量的记录，你的跨日志是巨大的

#1