使用 DELETE 刪除行

DELETE 語(yǔ)句可刪除表或視圖中的一行或多行。DELETE 語(yǔ)法的簡(jiǎn)化形式為：

DELETE table_or_view
FROM table_sources
WHERE search_condition

• table_or_view 指定要從中刪除行的表或視圖面哼。table_or_view 中所有符合 WHERE 搜索條件的行都將被刪除。如果沒有指定 WHERE 子句杂瘸，將刪除 table_or_view 中的所有行骨稿。
• FROM 子句指定可由 WHERE 子句搜索條件中的謂詞使用的其他表或視圖及聯(lián)接條件（以關(guān)聯(lián)表為條件刪除另一條的數(shù)據(jù)）座菠，以限定要從 table_or_view 中刪除的行仪媒。不會(huì)從 FROM 子句指定的表中刪除行沉桌，只從 table_or_view 指定的表中刪除行。詳見示例(3)算吩。

鎖定行為
??默認(rèn)情況下留凭，DELETE 語(yǔ)句始終在其修改的表上獲取排他 (X) 鎖并在事務(wù)完成之前持有該鎖。 使用排他鎖（X 鎖）時(shí)赌莺，任何其他事務(wù)都無法修改數(shù)據(jù)；僅在使用 NOLOCK 提示或未提交讀隔離級(jí)別時(shí)才會(huì)進(jìn)行讀取操作松嘶。
??從堆中刪除行時(shí)艘狭，數(shù)據(jù)庫(kù)引擎可以使用行鎖定或頁(yè)鎖定進(jìn)行操作。結(jié)果翠订，刪除操作導(dǎo)致的空頁(yè)將繼續(xù)分配給堆巢音。未釋放空頁(yè)時(shí)，數(shù)據(jù)庫(kù)中的其他對(duì)象將無法重用關(guān)聯(lián)的空間尽超。
??若要?jiǎng)h除堆中的行并釋放頁(yè)官撼，請(qǐng)使用下列方法之一。

• 在 DELETE 語(yǔ)句中指定 TABLOCK 提示似谁。使用 TABLOCK 提示會(huì)導(dǎo)致刪除操作獲取表的共享鎖傲绣，而不是行鎖或頁(yè)鎖掠哥。這將允許釋放頁(yè)。
• 如果要從表中刪除所有行秃诵，請(qǐng)使用 TRUNCATE TABLE续搀。
• 刪除行之前，請(qǐng)對(duì)堆創(chuàng)建聚集索引菠净。刪除行之后禁舷，可以刪除聚集索引。與先前的方法相比毅往，此方法非常耗時(shí)牵咙，并且使用更多的臨時(shí)資源。

日志記錄行為
??DELETE 語(yǔ)句每次刪除一行攀唯，并在事務(wù)日志中為所刪除的每行記錄一個(gè)項(xiàng)洁桌。

【示例】
(1)下面的示例從 SalesPersonQuotaHistory 表中刪除所有行，因?yàn)樵摾词褂?WHERE 子句限制刪除的行數(shù)革答。

DELETE FROM Sales.SalesPersonQuotaHistory

(2)下面的示例從 ProductCostHistory 表中刪除 StandardCost 列的值大于 1000.00 的所有行战坤。

DELETE FROM Production.ProductCostHistory WHERE StandardCost > 1000.00

(3)從 SalesPersonQuotaHistory 表中刪除行，該表基于 SalesPerson 表中所存儲(chǔ)的本年度迄今為止的銷售業(yè)績(jī)残拐。第一條 DELETE 語(yǔ)句顯示與 ISO 兼容的子查詢解決方案途茫，第二條 DELETE 語(yǔ)句顯示 Transact-SQL 擴(kuò)展插件。

-- SQL-2003 Standard subquery
DELETE FROM Sales.SalesPersonQuotaHistory 
WHERE BusinessEntityID IN 
    (SELECT BusinessEntityID 
     FROM Sales.SalesPerson 
     WHERE SalesYTD > 2500000.00);

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

使用 TRUNCATE TABLE 刪除所有行

若要?jiǎng)h除表中的所有行溪食，則 TRUNCATE TABLE 語(yǔ)句是一種快速囊卜、有效的方法。TRUNCATE TABLE 與不含 WHERE 子句的 DELETE 語(yǔ)句類似错沃。但是栅组，TRUNCATE TABLE 速度更快，并且使用更少的系統(tǒng)資源和事務(wù)日志資源枢析。
??與 DELETE 語(yǔ)句相比玉掸，TRUNCATE TABLE 具有以下優(yōu)點(diǎn)：

• 所用的事務(wù)日志空間較少。
  DELETE 語(yǔ)句每次刪除一行醒叁，并在事務(wù)日志中為所刪除的每行記錄一個(gè)項(xiàng)司浪。TRUNCATE TABLE 通過釋放用于存儲(chǔ)表數(shù)據(jù)的數(shù)據(jù)頁(yè)來刪除數(shù)據(jù)，并且在事務(wù)日志中只記錄頁(yè)釋放把沼。
• 使用的鎖通常較少啊易。
  當(dāng)使用行鎖執(zhí)行 DELETE 語(yǔ)句時(shí)，將鎖定表中各行以便刪除饮睬。TRUNCATE TABLE 始終鎖定表和頁(yè)租谈，而不是鎖定各行。
• 如無例外捆愁，在表中不會(huì)留有任何頁(yè)割去。
  執(zhí)行 DELETE 語(yǔ)句后窟却，表仍會(huì)包含空頁(yè)。盡管這些頁(yè)會(huì)通過后臺(tái)清除進(jìn)程迅速釋放劫拗。

下面的示例刪除 JobCandidate 表中的所有數(shù)據(jù)间校。在 TRUNCATE TABLE 語(yǔ)句之前和之后使用 SELECT 語(yǔ)句來比較結(jié)果。

SELECT COUNT(*) AS BeforeTruncateCount 
FROM HumanResources.JobCandidate;
GO
TRUNCATE TABLE HumanResources.JobCandidate;
GO
SELECT COUNT(*) AS AfterTruncateCount 
FROM HumanResources.JobCandidate;
GO

刪除結(jié)果集中的行

ADO页慷、OLE DB 和 ODBC API 支持從結(jié)果集中刪除應(yīng)用程序所在的當(dāng)前行憔足。應(yīng)用程序執(zhí)行某個(gè)語(yǔ)句，然后從結(jié)果集中提取行酒繁。應(yīng)用程序提取行后滓彰，就可以使用以下函數(shù)或方法刪除該行：

• ADO 應(yīng)用程序使用 Recordset 對(duì)象的 Delete 方法。
• OLE DB 應(yīng)用程序使用 IRowsetChange 接口的** DeleteRows **方法州袒。
• ODBC 應(yīng)用程序使用帶 SQL_DELETE 選項(xiàng)的 SQLSetPos 函數(shù)揭绑。
• DB-library 應(yīng)用程序使用 **dbcursor **執(zhí)行 **CRS_DELETE **操作。

Transact-SQL 腳本郎哭、存儲(chǔ)過程和觸發(fā)器可以使用DELETE語(yǔ)句中的 WHERE CURRENT OF子句來刪除它們當(dāng)前所在的游標(biāo)行他匪。以下示例使用名為complex_cursor的游標(biāo)從 EmployeePayHistory 表中刪除一行。DELETE 只影響當(dāng)前從游標(biāo)中提取的一行夸研。

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

使用 TOP 限制刪除的行

可以使用 TOP 子句限制 DELETE 語(yǔ)句中刪除的行數(shù)邦蜜。當(dāng) TOP (n) 子句與 DELETE 一起使用時(shí)，將針對(duì)隨機(jī)選擇的第 n 行執(zhí)行刪除操作亥至。
??例如悼沈，下面的語(yǔ)句從 PurchaseOrderDetail 表中刪除了其到期日期早于 2002 年 7 月 1 日的 20 個(gè)隨機(jī)行。

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

在將 TOP 與 DELETE 結(jié)合使用時(shí)姐扮，被引用行不按任何順序排列絮供，不能直接在此語(yǔ)句中指定 ORDER BY 子句。 如果需要使用 TOP 來刪除按有意義的時(shí)間順序排列的行茶敏，您必須同時(shí)在嵌套 select 語(yǔ)句中使用 TOP 和 ORDER BY 子句壤靶。
??下面的查詢從 PurchaseOrderDetail 表中刪除了其到期日期最早的 10 行。為了確保僅刪除 10 行惊搏，嵌套 Select 語(yǔ)句 (PurchaseOrderID) 中指定的列將成為表的主鍵贮乳。如果指定列包含重復(fù)的值，則在嵌套 Select 語(yǔ)句中使用非鍵列可能會(huì)導(dǎo)致刪除的行超過 10 個(gè)胀屿。

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

DELETE (Transact-SQL)

-- Syntax for SQL Server and Azure SQL Database  
  
[ 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> ]  
    [ FROM table_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   
}  

• TOP (expression) [ PERCENT ]
  指定將要?jiǎng)h除的任意行數(shù)或任意行的百分比塘揣。 expression 可以是行數(shù)或行的百分比包雀。
• server_name：表或視圖所在的鏈接服務(wù)器的名稱宿崭。
• database_name：數(shù)據(jù)庫(kù)的名稱。
• schema_name：表或視圖所屬架構(gòu)的名稱才写。
• table_or view_name：要從中刪除行的表或視圖的名稱葡兑。
• <OUTPUT_Clause>：OUTPUT子句
• table_source：指定可由 WHERE 子句搜索條件中的謂詞使用的其他表或視圖及聯(lián)接條件（以關(guān)聯(lián)表為條件刪除另一條的數(shù)據(jù)）奖蔓，以限定要從 table_or_view 中刪除的行。不會(huì)從 FROM 子句指定的表中刪除行讹堤，只從 table_or_view 指定的表中刪除行吆鹤。

【示例】
??一些基本的示例在上面已經(jīng)演示過，比如T-SQL擴(kuò)展插件寫法(DELETE table_or_view FROM table_sources)洲守、從游標(biāo)中刪除結(jié)果集中的行疑务、使用Top限制刪除行等。下面示例使用OUTPUT子句捕獲 DELETE 語(yǔ)句的結(jié)果梗醇。

A. 使用帶有 OUTPUT 子句的 DELETE

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

B. 在 DELETE 語(yǔ)句中同時(shí)使用 OUTPUT 與 <from_table_name>
??從 ProductProductPhoto表中刪除行知允，該表基于Product表中所存儲(chǔ)的ProductModelID。OUTPUT 子句返回所刪除表中的列（ DELETED.ProductID叙谨、 DELETED.ProductPhotoID）以及 Product 表中的列温鸽。

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;  
 
GO