SQL Server 截断表

SQL Server 中的 TRUNCATE TABLE 语句是一个 DDL（数据定义语言）命令。 此语句用于从表中删除所有行或指定的分区，而不会删除表结构。 它类似于 DELETE 命令，但它不允许过滤表记录，因为我们不能将 WHERE 子句与此命令一起使用。 但是，就时间和资源使用而言，TRUNCATE 比 DELETE 快得多。

此命令在删除表中的数据的同时，会释放数据页的分配。 它类似于再次删除和重新创建表。 与 DELETE 语句不同，它仅在事务日志中记录页面释放，而不是逐行记录。

在使用 TRUNCATE 命令时，我们应该考虑以下几点

• 它不支持 WHERE 子句，因此无法过滤记录。
• 它不维护操作日志，因此我们无法在执行此命令后回滚已删除的数据。
• 当表被外键引用或参与索引视图时，它不能使用。
• 它不能触发 DELETE 触发器，如 DELETE 命令。 因此，当系统需要触发删除触发器时，我们不能使用此命令。

语法

以下语法解释了用于从表中删除数据的 TRUNCATE 命令。

此处 schema_name 表示存储表的模式的名称。 这是一个可选参数。 table_name 是要截断数据的表的名称。

如果我们想在一个语句中截断多个表，我们需要为每个表单独执行截断语句。

TRUNCATE Table 示例

让我们通过一个例子来演示如何使用该表截断。 首先，我们将通过执行以下操作来创建一个“employee”表

接下来，我们将按如下方式将一些数据插入到该表中

我们可以使用 SELECT 语句验证插入的数据

假设我们需要删除该表的所有数据，但不想删除其结构。 在这种情况下，SQL Server 提供了 TRUNCATE TABLE 语句，如下所示

再次执行 SELECT 语句以验证数据的删除。 此命令产生以下输出，显示表列名，而表中没有记录

如何在 SQL Server 中回滚 TRUNCATE？

正如我们所知，在提交后我们无法回滚截断操作。 但是，SQL Server 使我们能够做一些事情来取回所有数据或至少取回部分数据。 这是由于数据在执行截断操作后仍存储在 MDF 文件中。 我们看不到这个文件，因为 SQL Server 将其用作可用空间。

我们可以通过读取释放的数据页并将其转换为可读数据来取回截断的数据，然后再用新数据覆盖可用空间。

在 SQL Server 中回滚截断数据的唯一方法是使用事务操作，如下所示

SELECT 语句将返回截断的数据。 请参阅以下输出

如何使用 FOREIGN key 截断表？

SQL Server 不允许对具有外键约束的表执行 TRUNCATE 操作。 但是，我们有一个解决方案可以做到这一点。 但这两个解决方案都存在丢失数据完整性的风险。

解决方案 1： 首先，删除约束，然后执行 TRUNCATE 操作。

解决方案 2： 登录到数据库服务器，并在 TRUNCATE 操作之前禁用外键检查，然后按如下方式重新启用

TRUNCATE TABLE 优于 DELETE 的优点

以下是 TRUNCATE 与 DELETE 操作相比的主要优点

更少的事务日志空间

DELETE 语句一次删除一行，并在事务日志中为每个已删除的行插入一个条目。 相比之下，TRUNCATE 语句释放页面，从而从表中删除数据，并且仅在事务日志中插入页面释放。

使用更少的锁

当我们使用行锁执行 DELETE 命令时，表中的每一行都会被锁定以进行删除。 另一方面，TRUNCATE 语句锁定表和页面，而不是单个行。

身份重置

如果使用 TRUNCATE 命令删除表数据，并且该表具有 IDENTITY 列，则该列的计数器将重置为种子值。 但是，DELETE 语句无法做到这一点。