sql-server – 为什么同时使用TRUNCATE和DROP？

在我工作的系统中,有许多存储过程和SQL脚本使用临时表.使用这些表后,最好放弃它们.

我的许多同事(几乎所有同事都比我更有经验)通常会这样做：

TRUNCATE TABLE #mytemp
DROP TABLE #mytemp

我通常在脚本中使用单个DROP TABLE.

在DROP之前立即进行TRUNCATE有什么好的理由吗？

解决方法

TRUNCATE和DROP在行为和速度上几乎相同,因此在DROP之前执行TRUNCATE是不必要的.

注意：我从SQL Server的角度编写了这个答案,并假设它同样适用于Sybase.看来this is not entirely the case.

注意：当我第一次发布这个答案时,还有其他一些评价很高的答案 – 包括当时接受的答案 – 这些答案产生了一些错误的声明：TRUNCATE未被记录; TRUNCATE无法回滚; TRUNCATE比DROP快;等等

既然已经清理了这个线程,那么后面的反驳似乎与原始问题相关.我把它们留在这里作为其他想要揭穿这些神话的人的参考.

有一些流行的谎言 – 即使在经验丰富的DBA中也很普遍 – 可能会激发这种TRUNCATE-then-DROP模式.他们是：

>神话：未记录TRUNCATE,因此无法回滚.
>神话：TRUNCATE比DROP快.

让我反驳这些谎言.我从SQL Server的角度来看这个反驳,但我在这里说的一切应该同样适用于Sybase.

记录TRUNCATE,可以回滚.

> TRUNCATE是一个记录操作,因此it can be rolled back.只需将其包装在一个事务中.

USE [tempdb];
SET NOCOUNT ON;

CREATE TABLE truncate_demo (
    whatever    VARCHAR(10)
);

INSERT INTO truncate_demo (whatever)
VALUES ('log this');

BEGIN TRANSACTION;
    TRUNCATE TABLE truncate_demo;
ROLLBACK TRANSACTION;

SELECT *
FROM truncate_demo;

DROP TABLE truncate_demo;

但请注意,这是Oracle的not true.虽然Oracle的撤消和重做功能记录并受其保护,但用户无法回滚TRUNCATE和其他DDL语句,因为Oracle会在所有DDL语句之前和之后立即发出implicit commits.
> TRUNCATE记录最少,而不是完全记录.那是什么意思？假设你TRUNCATE一个表. TRUNCATE不是将每个已删除的行放在事务日志中,而只是将它们所在的数据页标记为未分配.这就是为什么它如此之快.这也是您无法使用日志阅读器从事务日志中恢复TRUNCATE-ed表的行的原因.所有你会发现有对已解除分配的数据页面的引用.

将此与DELETE进行比较.如果删除表中的所有行并提交事务,理论上您仍然可以在事务日志中找到已删除的行并从那里恢复它们.这是因为DELETE将每个已删除的行写入事务日志.对于大型表,这将使它比TRUNCATE慢得多.

DROP和TRUNCATE一样快.

>与TRUNCATE类似,DROP是一种最低限度记录的操作.这意味着DROP也可以回滚.这也意味着it works exactly the same way为TRUNCATE. DROP不会删除单个行,而是将相应的数据页标记为未分配,并另外将表的元数据标记为已删除.
>因为TRUNCATE和DROP的工作方式完全相同,所以它们的运行速度与其他方式一样快.在DROP-ing之前没有必要对一个表进行TRUNCATE.如果您不相信我,请在您的开发实例上运行this demo script.

在我的本地机器上有一个热缓存,我得到的结果如下：

table row count: 134,217,728

run#        transaction duration (ms)
      TRUNCATE   TRUNCATE then DROP   DROP
==========================================
01       0               1             4
02       0              39             1
03       0               1             1
04       0               2             1
05       0               1             1
06       0              25             1
07       0               1             1
08       0               1             1
09       0               1             1
10       0              12             1
------------------------------------------
avg      0              8.4           1.3

因此,对于一个1.34亿行表,DROP和TRUNCATE实际上都没有时间. (在冷缓存中,第一次运行或两次运行大约需要2-3秒.)我还认为TRUNCATE然后DROP操作的平均持续时间较长可归因于我本地计算机上的负载变化而不是因为组合是某种方式神奇地比个别行动差一个数量级.毕竟,它们几乎完全相同.

如果您对这些操作的日志记录开销感兴趣,请参阅Martin has a straightforward explanation.

（编辑：财气旺网 - 海宁网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!