SQL 删除表和截断表之间的区别？

Difference between drop table and truncate table?

提问by Brian G

I have some tables that I build as a part of my report rollup. I don't need them afterwards at all. Someone mentioned to truncate them as it would be faster.

我有一些表格是作为我的报告汇总的一部分构建的。之后我根本不需要它们。有人提到要截断它们，因为它会更快。

回答by JoshL

Deleting records from a table logs every deletion and executes delete triggers for the records deleted. Truncate is a more powerful command that empties a table without logging each row. SQL Server prevents you from truncating a table with foreign keys referencing it, because of the need to check the foreign keys on each row.

从表中删除记录会记录每次删除并为删除的记录执行删除触发器。截断是一个更强大的命令，它清空表而不记录每一行。SQL Server 阻止您截断带有引用它的外键的表，因为需要检查每一行的外键。

Truncate is normally ultra-fast, ideal for cleaning out data from a temporary table. It does preserve the structure of the table for future use.

截断通常是超快的，非常适合从临时表中清除数据。它确实保留了表的结构以备将来使用。

If you actually want to remove the table definitionsas well as the data, simply drop the tables.

如果您确实要删除表定义以及数据，只需删除表即可。

See this MSDN articlefor more info

有关更多信息，请参阅此 MSDN 文章

回答by ceejayoz

DROP TABLE deletes the table.

DROP TABLE 删除表。

TRUNCATE TABLE empties it, but leaves its structure for future data.

TRUNCATE TABLE 将其清空，但为将来的数据保留其结构。

回答by daremon

DROP and TRUNC do different things:

DROP 和 TRUNC 做不同的事情：

TRUNCATE TABLE

截断表

Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

从表中删除所有行，而不记录单个行的删除。TRUNCATE TABLE 类似于没有 WHERE 子句的 DELETE 语句；但是，TRUNCATE TABLE 速度更快，使用的系统和事务日志资源更少。

DROP TABLE

掉落表

Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables.

删除一个或多个表定义以及这些表的所有数据、索引、触发器、约束和权限规范。

As far as speed is concerned the difference should be small. And anyway if you don't need the table structure at all, certainly use DROP.

就速度而言，差异应该很小。无论如何，如果您根本不需要表结构，请务必使用 DROP。

回答by Horcrux7

I think you means the difference between DELETE TABLE and TRUNCATE TABLE.

我认为您的意思是 DELETE TABLE 和 TRUNCATE TABLE 之间的区别。

DROP TABLE

掉落表

remove the table from the database.

从数据库中删除表。

DELETE TABLE

删除表

without a condition delete all rows. If there are trigger and references then this will process for every row. Also a index will be modify if there one.

没有条件删除所有行。如果有触发器和引用，那么这将对每一行进行处理。如果有索引，也会修改索引。

TRUNCATE TABLE

截断表

set the row count zero and without logging each row. That it is many faster as the other both.

将行数设置为零并且不记录每一行。它比其他两者都快得多。

回答by Nathan Feger

None of these answer point out an important difference about these two operations. Drop table is an operation that can be rolled back. However, truncate cannot be rolled back['TRUNCATE TABLE' can be rolled back as well]. In this way dropping a very large table can be very expensive if there are many rows, because they all have to be recorded in a temporary space in case you decide to roll it back.

这些答案都没有指出这两种操作的重要区别。删除表是一种可以回滚的操作。 但是，truncate 不能回滚['TRUNCATE TABLE' 也可以回滚]。如果有很多行，以这种方式删除非常大的表可能会非常昂贵，因为它们都必须记录在临时空间中，以防您决定回滚它。

Usually, if I want to get rid of a large table, I will truncate it, then drop it. This way the data will be nixed without record, and the table can be dropped, and that drop will be very inexpensive because no data needs to be recorded.

通常，如果我想摆脱一个大表，我会截断它，然后将其删除。这样，数据将在没有记录的情况下被 nixed，并且可以删除表，并且删除将非常便宜，因为不需要记录数据。

It is important to point out though that truncate just deletes data, leaving the table, while drop will, in fact, delete the data and the table itself. (assuming foreign keys don't preclude such an action)

重要的是要指出，虽然 truncate 只是删除数据，离开表，而 drop 实际上会删除数据和表本身。（假设外键不排除这样的操作）

回答by Kevin Fairchild

TRUNCATE TABLE keeps all of your old indexing and whatnot. DROP TABLE would, obviously, get rid of the table and require you to recreate it later.

TRUNCATE TABLE 保留所有旧的索引和诸如此类的东西。显然，DROP TABLE 会删除该表并要求您稍后重新创建它。

回答by DMKing

Drop gets rid of the table completely, removing the definition as well. Truncate empties the table but does not get rid of the definition.

Drop 完全摆脱了表格，也删除了定义。截断会清空表，但不会删除定义。

回答by Dan Udey

Truncating the table empties the table. Dropping the table deletes it entirely. Either one will be fast, but dropping it will likely be faster (depending on your database engine).

截断表会清空表。删除表会将其完全删除。任何一个都会很快，但删除它可能会更快（取决于您的数据库引擎）。

If you don't need it anymore, drop it so it's not cluttering up your schema.

如果您不再需要它，请将其删除，以免弄乱您的架构。

回答by Raja

DELETE TableA instead of TRUNCATE TableA? A common misconception is that they do the same thing. Not so. In fact, there are many differences between the two.

删除 TableA 而不是 TRUNCATE TableA？一个常见的误解是他们做同样的事情。不是这样。事实上，两者之间有很多不同之处。

DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted.

DELETE 是基于每行的记录操作。这意味着每一行的删除都会被记录下来并被物理删除。

You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.

您可以删除任何不会违反约束的行，同时保留外键或任何其他约束。

TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE.

TRUNCATE 也是一个记录操作，但方式不同。TRUNCATE 记录数据所在数据页的重新分配。数据页的解除分配意味着您的数据行仍然实际存在于数据页中，但范围已被标记为空以供重用。这就是使 TRUNCATE 比 DELETE 执行的操作更快的原因。

You cannot TRUNCATE a table that has any foreign key constraints. You will have to remove the contraints, TRUNCATE the table, and reapply the contraints.

您不能 TRUNCATE 具有任何外键约束的表。您将必须删除约束，截断表，然后重新应用约束。

TRUNCATE will reset any identity columns to the default seed value.

TRUNCATE 将任何标识列重置为默认种子值。

回答by Optimizer

DROP Table

删除表

DROP TABLE [table_name];

The DROP command is used to remove a table from the database. It is a DDL command. All the rows, indexes and privileges of the table will also be removed. DROP operation cannot be rolled back.

DROP 命令用于从数据库中删除表。它是一个 DDL 命令。表的所​​有行、索引和权限也将被删除。DROP 操作无法回滚。

DELETE Table

删除表

DELETE FROM [table_name]
WHERE [condition];

DELETE FROM [table_name];

The DELETE command is a DML command. It can be used to delete all the rows or some rows from the table based on the condition specified in WHERE clause. It is executed using a row lock, each row in the table is locked for deletion. It maintain the transaction log, so it is slower than TRUNCATE. DELETE operations can be rolled back.

DELETE 命令是 DML 命令。它可用于根据 WHERE 子句中指定的条件从表中删除所有行或某些行。它使用行锁执行，表中的每一行都被锁定以进行删除。它维护事务日志，所以它比 TRUNCATE 慢。DELETE 操作可以回滚。

TRUNCATE Table

截断表

TRUNCATE TABLE [table_name];

The TRUNCATE command removes all rows from a table. It won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster than DELETE. It is executed using a table lock and whole table is locked for remove all records. It is a DDL command. TRUNCATE operations cannot be rolled back.

TRUNCATE 命令从表中删除所有行。它不会记录每一行的删除，而是记录表的数据页的释放，这使得它比 DELETE 更快。它使用表锁执行，整个表被锁定以删除所有记录。它是一个 DDL 命令。TRUNCATE 操作无法回滚。