SQL Server 删除数据详解：使用 DELETE 删除记录与 TRUNCATE 的区别

在数据库操作中，删除数据是至关重要的一部分，特别是在处理无效数据、进行数据清理或业务逻辑需求的情况下。SQL Server 提供了两种主要的删除数据的方式：DELETE 和 TRUNCATE。这两者在功能上有显著的区别，适用于不同的使用场景。

本文将详细介绍如何使用 DELETE 和 TRUNCATE 来删除 SQL Server 中的记录，提供详细的示例，涵盖它们所有的使用，解释它们的差异，并结合事务和其他 SQL Server 组件进行讲解。

1. DELETE 语句简介

DELETE 语句用于从表中删除一行或多行数据，删除操作可以根据指定的条件来执行。DELETE 是一种 DML（数据操作语言）操作，允许根据条件部分删除表中的数据。

DELETE 语句的基本语法：

DELETE FROM 表名
WHERE 条件;

• 表名：表示要删除数据的表。
• WHERE：用于指定删除的条件。如果省略 WHERE，表中的所有数据行都会被删除。

DELETE 语句可以与 WHERE 子句结合使用，删除满足条件的特定记录，也可以在事务中执行，确保数据的安全性和可回滚性。

2. DELETE 使用详解

2.1 删除单条记录

假设我们有一个 Employees 表，如下所示：

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Salary DECIMAL(18, 2),
    HireDate DATETIME
);

我们想要删除姓氏为 Doe 的员工记录，可以使用以下 DELETE 语句：

DELETE FROM Employees
WHERE LastName = 'Doe';

解释：

• 此语句删除所有姓氏为 Doe 的员工记录。
• WHERE 子句限制了只删除满足条件的记录。

2.2 删除所有记录

如果我们想要删除表中的所有记录，但保留表的结构，可以省略 WHERE 子句：

DELETE FROM Employees;

解释：

• 此操作会删除 Employees 表中的所有数据，但保留表的定义和结构。
• 因为没有 WHERE 子句，所有记录都会被删除。

3. DELETE 的进阶使用

3.1 使用多条件的 DELETE

可以通过 AND 或 OR 来组合多个条件，精确地删除符合多个条件的记录。

DELETE FROM Employees
WHERE Salary < 50000.00 AND HireDate < '2020-01-01';

解释：

• 此语句删除所有工资低于 50000.00 且入职日期早于 2020-01-01 的员工记录。

3.2 使用子查询删除

有时我们需要根据另一个表中的条件删除数据，可以使用子查询实现复杂的删除操作。

DELETE FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID FROM Salaries WHERE NewSalary > 70000.00);

解释：

• 通过子查询，删除所有员工编号 (EmployeeID) 符合条件的记录。这些条件是基于 Salaries 表中新工资超过 70000.00 的记录。

3.3 使用 OUTPUT 返回被删除的数据

DELETE 语句可以与 OUTPUT 子句结合使用，返回被删除的数据，这对调试和数据跟踪非常有用。

DELETE FROM Employees
OUTPUT deleted.EmployeeID, deleted.LastName
WHERE Salary < 40000.00;

解释：

• OUTPUT 子句返回所有工资低于 40000.00 的员工编号 (EmployeeID) 和姓氏 (LastName)，这些员工的数据同时也被删除。

4. TRUNCATE 语句简介

TRUNCATE 是一种 DDL（数据定义语言）操作，专门用于快速删除表中的所有记录。与 DELETE 不同，TRUNCATE 不会逐行删除数据，而是通过重置表空间来清空表。它的执行速度比 DELETE 快得多，但有一些限制。

TRUNCATE 的基本语法：

TRUNCATE TABLE 表名;

• 表名：要清空数据的表名。

特点：

• TRUNCATE 只能删除整个表的所有记录，不能与 WHERE 子句一起使用。
• TRUNCATE 不会触发表上的 DELETE 触发器，因为它不是逐行删除数据。
• 删除后，表的自增（IDENTITY）列会被重置。

5. DELETE 与 TRUNCATE 的区别

虽然 DELETE 和 TRUNCATE 都可以删除表中的数据，但它们在实现和性能上有一些重要的区别。

6. DELETE 与 TRUNCATE 的实际场景示例

6.1 使用 DELETE 删除特定记录

假设我们只想删除那些工资低于某个数值的员工记录，而不影响其他员工，我们可以使用 DELETE：

DELETE FROM Employees
WHERE Salary < 50000.00;

解释：

• 此操作只会删除工资低于 50000.00 的员工记录，保留其他员工数据。

6.2 使用 TRUNCATE 清空表

当我们不关心表中的数据，且希望快速清空表内容时，可以使用 TRUNCATE：

TRUNCATE TABLE Employees;

解释：

• 该操作将快速清空 Employees 表中的所有数据，并重置 IDENTITY 列。

6.3 使用事务回滚删除操作

在执行 DELETE 或 TRUNCATE 操作时，通常可以将它们放在事务中，以便在出错时能够回滚操作，确保数据完整性。

BEGIN TRANSACTION;

DELETE FROM Employees
WHERE Salary < 50000.00;

-- 如果发生错误，可以使用 ROLLBACK 回滚操作
-- ROLLBACK TRANSACTION;

-- 提交事务，确认删除操作
COMMIT TRANSACTION;

解释：

• 通过 BEGIN TRANSACTION 和 COMMIT TRANSACTION，我们可以确保只有在操作成功时才提交删除操作。
• 如果出现错误，可以使用 ROLLBACK TRANSACTION 回滚删除操作。

7. 使用触发器处理删除操作

在实际业务场景中，我们可能希望在删除数据时执行某些额外的操作，比如记录日志或更新相关表。SQL Server 提供了触发器机制，可以在 DELETE 语句执行时自动执行自定义操作。

7.1 创建 DELETE 触发器

下面是一个简单的 DELETE 触发器示例，用于在删除员工记录时，将这些记录存储到一个历史表中：

CREATE TRIGGER trg_AfterDelete
ON Employees
AFTER DELETE
AS
BEGIN
    INSERT INTO EmployeesHistory (EmployeeID, FirstName, LastName, Salary, HireDate, DeletedDate)
    SELECT EmployeeID, FirstName, LastName, Salary, HireDate, GETDATE()
    FROM deleted;
END;

解释：

• 该触发器在每次执行 DELETE 时，都会将被删除的员工记录插入到 EmployeesHistory 表中，并记录删除的日期。

8. 删除外键约束记录

当涉及外键约束时，删除记录时可能会遇到问题，因为相关联的外键数据无法直接删除。为了解决这个问题，SQL Server 提供了多种策略，如使用级联删除（ON DELETE CASCADE）。

8.1 创建带有级联删除的表

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    EmployeeID INT,
    OrderDate DATETIME,
    CONSTRAINT FK_EmployeeID FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) ON DELETE CASCADE
);

解释：

• 在此

表中，如果删除了 Employees 表中的某个员工，所有与该员工关联的订单也会自动被删除。

总结

在 SQL Server 中，DELETE 和 TRUNCATE 提供了删除数据的两种主要方法。DELETE 允许精细地删除满足特定条件的记录，并且可以与事务和触发器结合使用，而 TRUNCATE 提供了一种快速清空表的方式，但适用场景较为有限。在选择合适的方法时，需要根据性能要求和业务逻辑来权衡。