SQL TRUNCATE 表

SQL Server TRUNCATE TABLE 命令是一个简单而强大的数据定义语言语句，它删除表或指定分区中的所有记录（行）。最重要的是，它会删除所有行，但表结构将保持不变。从定义上看，SQL TRUNCATE TABLE 语句类似于没有 WHERE 子句的 DELETE 命令，但它们之间存在许多差异，我们在本文中将详细解释。例如，DELETE 一次删除一行，而 TRUNCATE 一次删除所有记录。在本文中，我们将向您展示如何编写 TRUNCATE TABLE 语法、实际示例、常见错误及其修复方法等。

SQL Server TRUNCATE TABLE 语句可以快速有效地从指定表中删除所有行的数据，但表的结构、约束、列和索引将保持不变。以下是使用此语句的一些基本情况、规则和优点。

  • 使用它来删除大量表中的数据或清空行。它可以提高性能。
  • 它不允许使用 WHERE 子句。
  • 与 DELETE 语句相比，此语句使用的事务更少。因为此语句会取消用于存储记录/数据的数据页的分配。事务日志将存储这些页面取消分配的记录。
  • SQL Server TRUNCATE TABLE 对表级别进行锁定（速度更快），而不是对每一行进行锁定。因此，在应用此语句后，将不会留下任何页面。
  • 如果表包含标识列，则该列的计数器将重置为第一个值。例如，我们有一个 ID INT IDENTITY(1, 1)，它包含 100 行/记录，并且我们对 ID 执行了 TRUNCATE 表。它将删除 ID 中的行或所有记录，并将 IDENTITY 重置为 1。
  • 请务必检查表上是否有任何依赖关系。如果有，请在此语句之前删除外键。
  • 除非将其包装在事务中，否则无法回滚。
  • 在使用 TRUNCATE TABLE 命令之前，请务必备份数据。

SQL TRUNCATE TABLE 语法

在示例之前，我将向您展示此 TRUNCATE TABLE 语句的基本语法。

TRUNCATE TABLE Database_Name.Schema_Name.Table_Name

如您所见，它是一个简单的单行语句，包含 SQL Server TRUNCATE TABLE 命令后跟表名。

  • Database_Name：包含它的数据库。
  • Schema_name：它的模式。
  • Table_Name：表名。

请参阅 DELETE 语句 和 Where 子句 在 SQL Server 中的文章，以了解 DELETE 方法。

SQL TRUNCATE TABLE 示例

我将向您展示一个简单的示例来演示 TRUNCATE TABLE 命令，以便您了解其工作功能。例如，您的数据库中有一个 employee 表，由于某些原因（不相关），您想要清理该表（删除所有行）。在这种情况下，您可以简单地编写以下查询，它就可以完成工作。

TRUNCATE TABLE employee;

为了更好地理解，我们首先 创建 一个名为 employee 的新表，并 插入 几条记录（6 条）。 SELECT 语句 将显示初始数据，然后我们使用 SQL TRUNCATE TABLE 删除数据。接下来，我们再次使用 SELECT 和 COUNT 函数 来显示表。

CREATE TABLE employee (
	Emp_id INT PRIMARY KEY,
	Name VARCHAR(100),
	Education VARCHAR(100),
	Salary MONEY
);

INSERT INTO employee VALUES (1, 'Jhon', 'Masters Degree', 2000000);
INSERT INTO employee VALUES (2, 'Nick', 'Degree', 800000);
INSERT INTO employee VALUES (3, 'Tracy', 'B.Tech', 150000);
INSERT INTO employee VALUES (4, 'Julie', 'MBA', 120000);
INSERT INTO employee VALUES (5, 'David', 'High School', 500000);
INSERT INTO employee VALUES (6, 'Bruce', 'Diploma', 700000);

SELECT * from employee;

TRUNCATE TABLE employee;

SELECT * from employee;

SELECT COUNT(*) AS Num_Columns FROM employee;
SQL TRUNCATE TABLE Command Example

在此，一旦您执行了“TRUNCATE TABLE employee”命令，它将清空 employee 表。因此，当您尝试使用 SELECT * FROM 语句和 COUNT 函数时，它只会返回零条记录。

Microsoft SQL Server 不允许使用单个 TRUNCATE TABLE 命令从多个表中删除记录。例如，以下示例将从两个表中删除数据。

TRUNCATE TABLE employee;

TRUNCATE TABLE customers;

SQL TRUNCATE TABLE WITH PARTITION

除了删除给定表中的所有记录外，您还可以使用 TRUNCATE TABLE WITH PARTITIONS 子句来删除特定分区。以下示例将删除第一个和第三个分区中的数据。

注意：要使用下面的代码，您必须先对表进行分区，然后再使用下面的查询。

TRUNCATE TABLE employee 
WITH (PARTITIONS (1, 3));

您还可以使用 WITH (PARTITIONS (1, 4 TO 10))；来删除 1、4、5、6、7、8、9、10 列。类似地，如果您使用 WITH (PARTITIONS (2 TO 4, 6))；来删除 2、4、5、6 列。

将 Identity 列重置为起始值

SQL TRUNCATE TABLE 语句会将标识列的值重置为其原始起始值（或种子值）。如果您的要求是保留标识值的连续性，您需要遵循以下两种方法之一。

  • 使用 DBCC CHECKIDENT。
  • 使用 DELETE 命令。

为了演示这一点，我们将创建一个 department 表并插入一些记录。代码（查询）如下。 

CREATE TABLE department (
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Name VARCHAR(100) NOT NULL
);
INSERT INTO department VALUES ('Administration');
INSERT INTO department VALUES ('Sales Manager');
INSERT INTO department VALUES ('HR');

SELECT * FROM department;

我将在此表上使用 SQL TRUNCATE TABLE 命令。

TRUNCATE TABLE department;

执行上述命令后，ID 标识列的值将重置为起始值，即 1。在某些情况下，我们可能需要在删除记录后保留标识列（ID）的当前值。在这种情况下，您可以使用 DBCC CHECKIDENT 手动重置标识列的值。

TRUNCATE TABLE department;

DBCC CHECKIDENT ('department', RESEED, 9);

上述代码会将下一个标识值重置为 9。为了显示相同的内容，让我插入两条记录。

INSERT INTO department VALUES (Shop Keeping);

INSERT INTO department VALUES (Clerk);
SQL TRUNCATE Table Resets Identity Column Example

第二个选项是使用 DELETE 命令，因为它不会重置标识值。

使用事务以避免数据丢失

如前所述，SQL Server TRUNCATE TABLE 命令的操作是不可逆的。这意味着一旦执行了命令，它就会删除所有数据，并且无法恢复。但是，您可以使用最流行的事务概念将其包装在其中。

BEGIN TRANSACTION;

TRUNCATE TABLE employee;

-- If something goes wrong, roll back to original
ROLLBACK;

-- If everything looks ok, commit the transaction (execute truncate)
COMMIT;

常见的 SQL TRUNCATE TABLE 错误：修复！

正如我们前面提到的，TRUNCATE TABLE 命令是一个简单而强大的命令，用于删除表中的所有记录。尽管它是一个简单的命令，但在使用它处理表时必须非常小心，因为您可能会遇到错误。以下是处理 TRUNCATE TABLE 语句时的一些常见错误以及修复它们的过程。

SQL TRUNCATE 表时出现外键错误

如果您尝试截断一个表，而该表有来自其他表的外键约束引用它，服务器将抛出错误。要修复此错误，我们有两个选项：

  • 在使用 TRUNCATE TABLE 之前，暂时删除外键。
  • 使用 DELETE 命令而不是 TRUNCATE。

为了演示此错误，我将创建两个简单的表，它们具有主键和外键引用。

CREATE TABLE department (
	Dept_id INT PRIMARY KEY, 
	Department_Name VARCHAR(100) NOT NULL
);
CREATE TABLE employee (
	Emp_id INT IDENTITY(1,1) PRIMARY KEY, 
	Name VARCHAR(100) NOT NULL,
	Education VARCHAR(100),
	Salary MONEY,Dept_id INT, 
	FOREIGN KEY (Dept_id) REFERENCES department(Dept_id)
);

INSERT INTO department VALUES (1, 'Administration');
INSERT INTO department VALUES (2, 'Sales Manager');
INSERT INTO department VALUES (3, 'HR');
INSERT INTO department VALUES (4, 'Sales Represent');

INSERT INTO employee VALUES ('Jhon', 'Masters Degree', 2000000, 1);
INSERT INTO employee VALUES ('Nick', 'Degree', 800000, 4);
INSERT INTO employee VALUES ('Tracy', 'B.Tech', 1500000, 2);
INSERT INTO employee VALUES ('Julie', 'MBA', 1200000, 3);
INSERT INTO employee VALUES ('David', 'High School', 500000, 4);
INSERT INTO employee VALUES ('Bruce', 'Diploma', 700000, 4);

如果您使用 SQL TRUNCATE 命令删除 department 表中的记录，您将收到外键约束的错误。因为 employee 表有一个 Dept_id 列，该列具有引用 department 表的外键约束。

TRUNCATE TABLE department;
Msg 4712, Level 16, State 1, Line 4
Cannot truncate table 'department' because it is being referenced by a FOREIGN KEY constraint.

删除外键并重新添加

要修复上述错误，第一个选项是删除 employee 表上的外键。接下来，编写 SQL TRUNCATE TABLE 语句删除记录。然后，使用 ALTER TABLE 命令将外键约束添加回来。

删除外键的查询。

ALTER TABLE employee
DROP CONSTRAINT [FK__employee__Dept_i__5EBF139D];

从 department 中删除数据的目的。

TRUNCATE TABLE department;

将外键约束添加到 employee 表。

ALTER TABLE employee WITH NOCHECK
ADD CONSTRAINT [FK__employee__Dept] FOREIGN KEY([Dept_id]) 
REFERENCES department([Dept_id]);

SELECT * FROM employee;
SELECT * FROM department;

如果不使用 WITH NOCHECK 语句，您将收到错误，因为它会检查不存在的相应父记录。

ALTER TABLE 语句与 FOREIGN KEY 约束“FK__employee__Dept”冲突。冲突发生在数据库“TutorialGateway”的表“department”的列“Dept_id”中。

Fixing Foreign Key Constraints in SQL TRUNCATE TABLE

使用 DELETE 命令

正如我们在以下部分提到的，即使两个表之间存在主键和外键关系，DELETE 命令也能正常工作。虽然此过程比 SQL TRUNCATE TABLE 语句慢，但您不必担心删除和添加约束。它会删除两个表（department 和依赖表 employee）中的数据。

DELETE FROM department;

始终备份数据

当您使用 TRUNCATE TABLE 语句时，必须始终备份您的表，因为它不可逆。例如，当您想从 employee 表中删除数据时，使用 INSERT INTO SELECT 语句备份表。

INSERT INTO employee_backup FROM employee;

执行上述语句后，您可以截断 employee 表。如果命令有问题，您可以从备份表中复制原始数据。

SQL TRUNCATE TABLE 不会触发触发器

如果要在要截断的表上存在任何触发器，此命令不会触发它们。如果您需要触发器执行某些操作，例如维护审计日志，请使用 DELETE 命令。为了演示示例，我们将使用我们在前面文章中创建的 AFTER DELETE 触发器。请使用超链接查看代码。

首先，我们使用在第一个示例中创建的 employee 表。接下来，以下查询将创建一个 audit 表，并在 employee 表上创建一个 AFTER DELETE 触发器。当您尝试 TRUNCATE employee 表时，它不会触发此 SQL 触发器。但是，如果您使用 DELETE 命令（DELETE FROM employee WHERE Emp_id = 3），它会触发触发器。

CREATE TABLE employeeAudit (
	Emp_id INT PRIMARY KEY,
	Name VARCHAR(100),
	Education VARCHAR(100),
	Salary MONEY,
	ServerName VARCHAR(100),
	[DeletedTime] Datetime
);

CREATE TRIGGER AfterDELETETrigger on [employee]
FOR DELETE 
AS DECLARE @EmpID INT, @EmpName VARCHAR(50),
	@EmpEducation VARCHAR(50), @EmpSalary MONEY;

SELECT @EmpID = del.Emp_id FROM DELETED del;
SELECT @EmpName = del.Name FROM DELETED del;
SELECT @EmpEducation = del.Education FROM DELETED del;
SELECT @EmpSalary = del.Salary FROM DELETED del;
  
INSERT INTO [employeeAudit]( 
       Emp_id,[Name],[Education],[Salary],[ServerName],[DeletedTime])
VALUES (@EmpID,@EmpName,@EmpEducation,@EmpSalary,
	CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50)), GETDATE());
PRINT 'We Successfully Fired the AFTER DELETE Triggers.'
GO

SQL TRUNCATE TABLE 和 DELETE 命令之间的主要区别

当我们查看 TRUNCATE TABLE 语句和 DELETE 命令的功能时，它们在根本上看起来相似，但它们之间存在一些主要区别。根据您的要求，您必须在它们之间进行选择。下表将显示 SQL Server TRUNCATE TABLE 和 DELETE 命令之间的主要区别，请仔细理解它们。

TRUNCATE TABLE删除
它是一个 DDL（数据定义语言）命令。它是一个 DML（数据操作语言）命令。
它会删除给定表中的所有记录。DELETE 命令将根据给定条件从特定表中删除一条或多条记录。
它不支持 WHERE 子句。它使用 WHERE 子句来定义删除行的任何条件。
如果表有外键引用，则无法截断该表。除非是级联键约束，否则您可以删除具有外键引用的行。
TRUNCATE 语句一次删除所有行。因此，性能更快。它一次删除一行并记录它们。因此，DELETE 的性能比 TRUNCATE 慢。
在处理大型数据集时，它的性能更快，因为它不记录每一行的删除。每一行删除都有事务日志，这会消耗时间和资源，性能较慢。
它消耗的事务空间更少。因为它只记录数据页。它消耗大量的事务空间用于日志。 
在锁定方面，SQL TRUNCATE table 语句比 DELETE 命令需要的锁更少。在删除所有记录之前，它在表级别执行锁定（速度更快），而不是在每一行上执行，然后释放。它执行每行删除的锁定机制，并一直持续到最后一条记录删除。这对整个表来说是一个成本很高的过程。
一旦执行此命令，您就无法恢复数据。 您可以回滚数据。
如果表中存在标识列，它会将标识列的值重置为原始值。它不会重置标识列的值。
它不会触发表上的任何触发器。它会触发表的触发器。事实上，当删除行发生时，它会触发 DELETE 触发器。
用户只需 ALTER TABLE 权限即可执行此操作。用户需要删除权限。
它不适用于索引视图。它适用于索引视图。检查

如果我们考虑前面创建的 employee 表，其中有六条记录。如果您的目标是删除所有六条记录，SQL TRUNCATE TABLE 是最佳选择。但是，如果您要删除 3 条记录，或者基于条件的记录删除（使用 WHERE 子句），DELETE 是最佳方法。

例如，删除薪水低于 1000000 的员工。

DELETE FROM employee WHERE Salary < 1000000

SQL Server TRUNCATE 与 DROP：主要区别

DROP 命令和 TRUNCATE TABLE 都可用于删除数据，但它们的操作和结果有很大不同。例如，DROP 命令会删除整个表及其结构。而 TRUNCATE 命令会删除表中的数据。因此，请理解它们之间的区别和操作模式（不同的目的）。

下表显示了 SQL Server TRUNCATE TABLE 语句和 DROP 命令之间的关键区别。它突出了要点，以便更好地理解。

TRUNCATE TABLEDROP
它是一个 DDL（数据定义语言）命令。它也是一个 DDL 命令。
TRUNCATE 语句会删除表中的所有记录或行。但是，表结构、列等将保持不变（保留）。另一方面，DROP 命令将完全删除表及其结构。它包括数据、列、索引等。
当您想从表中删除所有数据时，使用此命令。当您想删除整个表或数据库时，使用此 DROP 命令。
如果存在引用该表的复合外键约束，则无法截断该表。同样，当其他表引用该表时，您无法删除该表。因此，首先删除或删除依赖项，然后应用操作。
它不会触发表上的任何触发器。它也不会触发表上的触发器。
它不会删除表上的触发器和键约束。它会删除表触发器和键约束。
用于该结构的表空间仍然存在于内存中。它会释放内存中的整个表空间。
它会重置标识列。此操作后没有表剩余。
SQL TRUNCATE TABLE 语句比 DELETE 和 DROP 命令都更快。DROP TABLE 命令的性能比 TRUNCATE 慢，因为它需要删除数据、表结构和整个表。但是，它比 DELETE 快。
用户需要 ALTER 权限来执行此操作。用户需要表及其模式的 ALTER 和 CONTROL 权限来执行此操作。
除非使用事务，否则无法回滚。执行 DROP 命令后根本无法回滚。
它维护最少的日志。通常，它会维护数据页的取消分配。它会记录整个表的删除。所以，会维护完整的日志。
WHERE 子句不适用。没有使用的意义。