DELETE (Transact-SQL)

从 SQL Server 2008 的表或视图中删除一行或多行。

Transact-SQL 语法约定

语法

[ WITH common_table_expression [ ,...n ] ]
DELETE 
    [ TOP (expression ) [ PERCENT ] ] 
    [ FROM ] 
    { { table_alias
      | <object> 
      | rowset_function_limited 
      [ WITH (table_hint_limited [ ...n ] ) ] } 
      | @table_variable
    }
    [ OUTPUT Clause ]
    [ FROMtable_source [ ,...n ] ] 
    [ WHERE { search_condition 
            | { [ CURRENT OF 
                   { { [ GLOBAL ] cursor_name } 
                       | cursor_variable_name 
                   } 
                ]
              }
            } 
    ] 
    [ OPTION (Query Hint [ ,...n ] ) ] 
[; ]

<object> ::=
{ 
    [ server_name.database_name.schema_name. 
      | database_name. [ schema_name ] . 
      | schema_name.
    ]
    table_or_view_name 
}

参数

• WITH common_table_expression
  指定在 DELETE 语句作用域内定义的临时命名结果集，也称为公用表表达式。结果集源自 SELECT 语句。有关详细信息，请参阅 WITH common_table_expression (Transact-SQL)。

• TOP ( expression) [ PERCENT ]
  指定将要删除的任意行数或任意行的百分比。expression 可以为行数或行的百分比。与 DELETE 一起使用的 TOP 表达式中被引用行将不按任何顺序排列。

  在 INSERT、UPDATE、MERGE 和 DELETE 语句中，需要使用括号分隔 TOP 中的 expression。有关详细信息，请参阅 TOP (Transact-SQL)。

• FROM
  可选的关键字，可用在 DELETE 关键字与目标对象之间。

• table_alias
  在表示要从中删除行的表或视图的 FROM table_source 子句中指定的别名。

• server_name
  表或视图所在的链接服务器的名称。server_name 可以指定为链接服务器名称，或者通过使用 OPENDATASOURCE 函数指定。

  在 server_name 指定为某一链接服务器时，需要 database_name 和 schema_name。如果使用 OPENDATASOURCE 指定 server_name，则 database_name 和 schema_name 可能不适用于所有数据源，并且受到访问远程对象的 OLE DB 访问接口的性能的限制。有关详细信息，请参阅分布式查询。

• database_name
  数据库的名称。

• schema_name
  该表或视图所属架构的名称。

• table_or view_name
  要删除行的表或视图的名称。

  table_or_view_name 引用的视图必须可更新，并且在该视图的 FROM 子句中只引用一个基表。有关可更新视图的详细信息，请参阅 CREATE VIEW (Transact-SQL)。

• rowset_function_limited
  OPENQUERY 或 OPENROWSET 函数，视提供程序的功能而定。有关访问接口所需功能的详细信息，请参阅 OLE DB 访问接口的 UPDATE 和 DELETE 语句要求。

• WITH (table_hint_limited [...n] )
  指定目标表允许的一个或多个表提示。需要有 WITH 关键字和括号。不允许使用 NOLOCK 和 READUNCOMMITTED。有关表提示的详细信息，请参阅表提示 (Transact-SQL)。

• @table\_variable
  指定表变量。

• <OUTPUT_Clause>
  将已删除行或基于这些行的表达式作为删除操作的一部分返回。在针对本地分区视图、分布式分区视图、远程表或远程视图的任何 DML 语句中都不支持 OUTPUT 子句。有关详细信息，请参阅 OUTPUT 子句 (Transact-SQL)。

• FROM table_source
  指定可用于将目标 table_or view_name 与 <table_source> 进行联接以便标识要删除的行的附加 FROM 子句。Transact-SQL 的这一对 DELETE 的扩展可在 WHERE 子句中取代子查询来标识要删除的行。

  有关详细信息，请参阅 FROM (Transact-SQL)。

• WHERE
  指定用于限制要删除的行数的条件。如果没有提供 WHERE 子句，则 DELETE 删除表中的所有行。

  基于 WHERE 子句中所指定的条件，有两种形式的删除操作：

  • 搜索删除指定搜索条件以限定要删除的行。例如，WHERE column_name = value。

  • 定位删除使用 CURRENT OF 子句指定游标。删除操作在游标的当前位置执行。这比使用 WHERE search_condition 子句限定要删除的行的搜索 DELETE 语句更为精确。如果搜索条件不唯一标识单行，则搜索 DELETE 语句删除多行。

• search_condition
  指定删除行的限定条件。对搜索条件中可以包含的谓词数量没有限制。有关详细信息，请参阅搜索条件 (Transact-SQL)。

• CURRENT OF
  指定 DELETE 在指定游标的当前位置执行。

• GLOBAL
  指定 cursor_name 涉及到全局游标。

• cursor_name
  从其中进行提取的打开游标的名称。如果同时存在名为 cursor_name 的全局游标和局部游标，那么，在指定了 GLOBAL 时，该参数是指全局游标；否则是指局部游标。游标必须允许更新。

• cursor_variable_name
  游标变量的名称。游标变量必须引用允许更新的游标。

• OPTION (query_hint [ ,...n] )
  指示优化器提示用于自定义数据库引擎处理语句的方式的关键字。有关详细信息，请参阅查询提示 (Transact-SQL)。

最佳做法

若要删除表中的所有行，请使用 TRUNCATE TABLE。TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。

使用 @@ROWCOUNT 函数可以将已删除行的数目返回到客户端应用程序。有关详细信息，请参阅@@ROWCOUNT (Transact-SQL)。

兼容性支持

在 SQL Server 的下一个版本中，使用 SET ROWCOUNT 将不会影响 DELETE 语句。请不要在新的开发工作中将 SET ROWCOUNT 与 DELETE 语句一起使用，并应修改当前使用它的应用程序以使用 TOP 语法。

错误处理

您可以通过在 TRY…CATCH 构造函数中指定 DELETE 语句，实现对该语句的错误处理。有关详细信息，请参阅在 Transact-SQL 中使用 TRY...CATCH。

如果 DELETE 语句违反了触发器，或试图删除另一个有 FOREIGN KEY 约束的表内的数据被引用行，则可能会失败。如果 DELETE 删除了多行，而在删除的行中有任何一行违反触发器或约束，则将取消该语句，返回错误且不删除任何行。

当 DELETE 语句遇到在表达式计算过程中发生的算术错误（溢出、被零除或域错误）时，数据库引擎将处理这些错误，就好象 SET ARITHABORT 设置为 ON。将取消批处理中的其余部分并返回错误消息。

互操作性

如果所修改的对象是表变量，则 DELETE 可用在用户定义函数的正文中。

删除包含 FILESTREAM 列的行时，会同时删除其基础文件系统文件。基础文件是由 FILESTREAM 垃圾回收器删除的。有关详细信息，请参阅使用 Transact-SQL 管理 FILESTREAM 数据。

不能在直接或间接引用对其定义 INSTEAD OF 触发器的视图的 DELETE 语句中指定 FROM 子句。有关 INSTEAD OF 触发器的详细信息，请参阅 CREATE TRIGGER (Transact-SQL)。

限制和局限

在 TOP 与 DELETE 一起使用时，引用的行将不按任何顺序排列，并且无法在此语句中直接指定 ORDER BY 子句。如果需要使用 TOP 来删除按有意义的时间顺序排列的行，您必须在嵌套 select 语句中同时使用 TOP 和 ORDER BY 子句。请参阅本主题后面的“示例”一节。

TOP 不能用于针对分区视图的 DELETE 语句中。

对远程表和本地及远程分区视图上的 DELETE 语句将忽略 SET ROWCOUNT 选项的设置。

锁定行为

从堆删除行时，数据库引擎 可以使用行锁定或页锁定进行操作。结果，删除操作导致的空页将继续分配给堆。未释放空页时，数据库中的其他对象将无法重用关联的空间。若要删除堆中的行并释放页，请使用下列方法之一。

• 在 DELETE 语句中指定 TABLOCK 提示。使用 TABLOCK 提示会导致删除操作获取表的共享锁，而不是行锁或页锁。这将允许释放页。有关 TABLOCK 提示的详细信息，请参阅表提示 (Transact-SQL)。

• 如果要从表中删除所有行，请使用 TRUNCATE TABLE。

• 删除行之前，请为堆创建聚集索引。删除行之后，可以删除聚集索引。与先前的方法相比，此方法非常耗时，并且使用更多的临时资源。

有关锁定的详细信息，请参阅数据库引擎中的锁定。

日志记录行为

DELETE 语句始终完整地记入日志。

权限

要求对目标表具有 DELETE 权限。如果语句包含 WHERE 子句，则还必须有 SELECT 权限。

DELETE 权限默认授予 sysadmin 固定服务器角色、db_owner 和 db_datawriter 固定数据库角色以及表所有者的成员。sysadmin、db_owner 和 db_securityadmin 角色成员以及表所有者可以将权限转让给其他用户。

示例

类别	作为特征的语法元素
基本语法	DELETE
指定要删除的行	WHERE 子句 • TOP • FROM 子句和子查询 • 游标 • WITH 公用表表达式
指定目标表，而非标准表	视图 • 表变量
向远程表中删除行	链接服务器 • OPENQUERY 行集函数 • OPENDATASOURCE 行集函数
通过使用提示覆盖查询优化器的默认行为	表提示
捕获 DELETE 语句的结果	OUTPUT 子句
在其他语句中使用 DELETE	存储过程 • MERGE

基本语法

本节中的示例说明了使用最低要求的语法的 DELETE 语句的基本功能。

下面的示例从 SalesPersonQuotaHistory 表中删除所有行，因为该例未使用 WHERE 子句限制删除的行数。

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

指定要删除的行

本节中的示例演示用于限制要删除的行数的方式。

A. 使用 WHERE 子句来限制行

下面的示例从 ProductCostHistory 表中删除 StandardCost 列的值大于 1000.00 的所有行。

USE AdventureWorks;
GO
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 1000.00;
GO

B. 使用 TOP 子句

可以使用 TOP 子句限制 DELETE 语句中删除的行数。当 TOP (n) 子句与 DELETE 一起使用时，将针对随机选择的第 n 行执行删除操作。

以下示例删除 ProductInventory 表中所有行（27 行）的 2.5%。

USE AdventureWorks;
GO
DELETE TOP (2.5) PERCENT 
FROM Production.ProductInventory;
GO

下面的示例从 PurchaseOrderDetail 表中删除了其到期日期早于 2002 年 7 月 1 日的 20 个随机行。

USE AdventureWorks;
GO
DELETE TOP (20) 
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO

如果需要使用 TOP 来删除按有意义的时间顺序排列的行，您必须同时使用 TOP 和 ORDER BY 子句。下面的示例从 PurchaseOrderDetail 表中删除了其到期日期最早的 10 行。为了确保仅删除 10 行，嵌套 Select 语句 (PurchaseOrderID) 中指定的列将成为表的主键。如果指定列包含重复的值，则在嵌套 Select 语句中使用非键列可能会导致删除的行超过 10 个。

USE AdventureWorks;
GO
DELETE FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetailID IN
   (SELECT TOP 10 PurchaseOrderDetailID 
    FROM Purchasing.PurchaseOrderDetail 
    ORDER BY DueDate ASC);
GO

C. 使用游标以便删除当前行

以下示例使用名为 complex_cursor 的游标删除 EmployeePayHistory 表中的单行。删除操作只影响当前从游标提取的单行。

USE AdventureWorks;
GO
DECLARE complex_cursor CURSOR FOR
    SELECT a.EmployeeID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.EmployeeID = b.EmployeeID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
DELETE FROM HumanResources.EmployeePayHistory
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO

D. 使用子查询并且使用 Transact-SQL FROM 扩展

下面的示例显示用于从基于联接或相关子查询的基表中删除记录的 Transact-SQL 扩展插件。第一条 DELETE 语句显示与 ISO 兼容的子查询解决方案，第二条 DELETE 语句显示 Transact-SQL 扩展插件。两个查询都将从 SalesPersonQuotaHistory 表中删除行，该表基于 SalesPerson 表中所存储的本年度迄今为止的销售业绩。

-- SQL-2003 Standard subquery

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
WHERE SalesPersonID IN 
    (SELECT SalesPersonID 
     FROM Sales.SalesPerson 
     WHERE SalesYTD > 2500000.00);
GO

-- Transact-SQL extension
USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
FROM Sales.SalesPersonQuotaHistory AS spqh
    INNER JOIN Sales.SalesPerson AS sp
    ON spqh.SalesPersonID = sp.SalesPersonID
WHERE sp.SalesYTD > 2500000.00;
GO

E. 使用公用表表达式

下面的示例只删除公用表表达式所返回的行。

指定目标表，而非标准表

本节中的示例说明如何通过指定视图或表变量来删除行。

从远程表中删除行

本节中的示例说明如何通过使用链接服务器或行集函数引用一个远程目标表，从该表中删除行。

通过使用提示覆盖查询优化器的默认行为

本节中的示例说明如何使用表提示和查询提示在处理 DELETE 语句时暂时覆盖查询优化器的默认行为。

注意
由于 SQL Server 查询优化器通常会为查询选择最佳执行计划，因此我们建议仅在最后迫不得已的情况下才可由资深的开发人员和数据库管理员使用提示。

以下示例指定表提示 READPAST。在指定 READPAST 时，行级锁定和页级锁定将被跳过，这导致数据库引擎不读取其他事务锁定的行和页。有关详细信息，请参阅表提示 (Transact-SQL)。

USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO

捕获 DELETE 语句的结果

本节中的示例说明如何使用 OUTPUT 子句从 DELETE 语句影响的每一行返回信息（或基于的表达式）。这些结果可以返回到处理应用程序，以供在确认消息、存档以及其他类似的应用程序要求中使用。

A. 使用带有 OUTPUT 子句的 DELETE

以下示例演示如何将 DELETE 语句的结果保存到一个表变量中。

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* 
WHERE ShoppingCartID = 20621;

--Verify all rows in the table that match the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;
GO

B. 在 DELETE 语句中同时使用 OUTPUT 与 from_table_name

以下示例根据 DELETE 语句的 FROM 子句中定义的搜索条件，删除 ProductProductPhoto 表中的行。OUTPUT 子句返回所删除表中的列（DELETED.ProductID、DELETED.ProductPhotoID）以及 Product 表中的列。在 FROM 子句中使用该项来指定要删除的行。

USE AdventureWorks;
GO
DECLARE @MyTableVar table (
    ProductID int NOT NULL, 
    ProductName nvarchar(50)NOT NULL,
    ProductModelID int NOT NULL, 
    PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
       p.Name,
       p.ProductModelID,
       DELETED.ProductPhotoID
    INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p 
    ON ph.ProductID = p.ProductID 
    WHERE p.ProductModelID BETWEEN 120 and 130;

--Display the results of the table variable.
SELECT ProductID, ProductName, ProductModelID, PhotoID 
FROM @MyTableVar
ORDER BY ProductModelID;
GO

请参阅

参考

CREATE TRIGGER (Transact-SQL)

INSERT (Transact-SQL)

SELECT (Transact-SQL)

TRUNCATE TABLE (Transact-SQL)

UPDATE (Transact-SQL)

WITH common_table_expression (Transact-SQL)

概念

FILESTREAM 概述