DELETE (Transact-SQL)
Removes one or more rows from a table or view in SQL Server 2012.
[ 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
}
To delete all the rows in a table, use TRUNCATE TABLE. TRUNCATE TABLE is faster than DELETE and uses fewer system and transaction log resources.
Use the @@ROWCOUNT function to return the number of deleted rows to the client application. For more information, see @@ROWCOUNT (Transact-SQL).
You can implement error handling for the DELETE statement by specifying the statement in a TRY…CATCH construct.
The DELETE statement may fail if it violates a trigger or tries to remove a row referenced by data in another table with a FOREIGN KEY constraint. If the DELETE removes multiple rows, and any one of the removed rows violates a trigger or constraint, the statement is canceled, an error is returned, and no rows are removed.
When a DELETE statement encounters an arithmetic error (overflow, divide by zero, or a domain error) occurring during expression evaluation, the Database Engine handles these errors as if SET ARITHABORT is set ON. The rest of the batch is canceled, and an error message is returned.
DELETE can be used in the body of a user-defined function if the object modified is a table variable.
When you delete a row that contains a FILESTREAM column, you also delete its underlying file system files. The underlying files are removed by the FILESTREAM garbage collector. For more information, see Access FILESTREAM Data with Transact-SQL.
The FROM clause cannot be specified in a DELETE statement that references, either directly or indirectly, a view with an INSTEAD OF trigger defined on it. For more information about INSTEAD OF triggers, see CREATE TRIGGER (Transact-SQL).
When TOP is used with DELETE, the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in this statement. If you need to use TOP to delete rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause in a subselect statement. See the Examples section that follows in this topic.
TOP cannot be used in a DELETE statement against partitioned views.
By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level. You can specify table hints to override this default behavior for the duration of the DELETE statement by specifying another locking method, however, we recommend that hints be used only as a last resort by experienced developers and database administrators. For more information, see Table Hints (Transact-SQL).
When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.
To delete rows in a heap and deallocate pages, use one of the following methods.
-
Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take an exclusive lock on the table instead of a row or page lock. This allows the pages to be deallocated. For more information about the TABLOCK hint, see Table Hints (Transact-SQL).
-
Use TRUNCATE TABLE if all rows are to be deleted from the table.
-
Create a clustered index on the heap before deleting the rows. You can drop the clustered index after the rows are deleted. This method is more time consuming than the previous methods and uses more temporary resources.
Permissions
DELETE permissions are required on the target table. SELECT permissions are also required if the statement contains a WHERE clause.
DELETE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. Members of the sysadmin, db_owner, and the db_securityadmin roles, and the table owner can transfer permissions to other users.
|
Category |
Featured syntax elements |
|---|---|
|
DELETE |
|
|
WHERE • FROM • cursor • |
|
|
Linked server • OPENQUERY rowset function • OPENDATASOURCE rowset function |
|
|
Overriding the default behavior of the query optimizer by using hints |
Table hints • query hints |
|
OUTPUT clause |
Basic Syntax
Examples in this section demonstrate the basic functionality of the DELETE statement using the minimum required syntax.
A. Using DELETE with no WHERE clause
The following example deletes all rows from the SalesPersonQuotaHistory table because a WHERE clause is not used to limit the number of rows deleted.
USE AdventureWorks2012; GO DELETE FROM Sales.SalesPersonQuotaHistory; GO
Limiting the Rows Deleted
Examples in this section demonstrate how to limit the number of rows that will be deleted.
A. Using the WHERE clause to delete a set of rows
The following example deletes all rows from the ProductCostHistory table in which the value in the StandardCost column is more than 1000.00.
USE AdventureWorks2012; GO DELETE FROM Production.ProductCostHistory WHERE StandardCost > 1000.00; GO
The following example shows a more complex WHERE clause. The WHERE clause defines two conditions that must be met to determine the rows to delete. The value in the StandardCost column must be between 12.00 and 14.00 and the value in the column SellEndDate must be null. The example also prints the value from the @@ROWCOUNT function to return the number of deleted rows.
USE AdventureWorks2012;
GO
DELETE Production.ProductCostHistory
WHERE StandardCost BETWEEN 12.00 AND 14.00
AND EndDate IS NULL;
PRINT 'Number of rows deleted is ' + CAST(@@ROWCOUNT as char(3));
B. Using a cursor to determine the row to delete
The following example deletes a single row from the EmployeePayHistory table using a cursor named my_cursor. The delete operation affects only the single row currently fetched from the cursor.
USE AdventureWorks2012;
GO
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
C. Using joins and subqueries to data in one table to delete rows in another table
The following examples show two ways to delete rows in one table based on data in another table. In both examples, rows from the SalesPersonQuotaHistory table based are deleted based on the year-to-date sales stored in the SalesPerson table. The first DELETE statement shows the ISO-compatible subquery solution, and the second DELETE statement shows the Transact-SQL FROM extension to join the two tables.
-- SQL-2003 Standard subquery
USE AdventureWorks2012;
GO
DELETE FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesYTD > 2500000.00);
GO
-- Transact-SQL extension USE AdventureWorks2012; GO 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; GO
A. Using TOP to limit the number of rows deleted
When a TOP (n) clause is used with DELETE, the delete operation is performed on a random selection of n number of rows. The following example deletes 20 random rows from the PurchaseOrderDetail table that have due dates that are earlier than July 1, 2006.
USE AdventureWorks2012; GO DELETE TOP (20) FROM Purchasing.PurchaseOrderDetail WHERE DueDate < '20020701'; GO
If you have to use TOP to delete rows in a meaningful chronological order, you must use TOP together with ORDER BY in a subselect statement. The following query deletes the 10 rows of the PurchaseOrderDetail table that have the earliest due dates. To ensure that only 10 rows are deleted, the column specified in the subselect statement (PurchaseOrderID) is the primary key of the table. Using a nonkey column in the subselect statement may result in the deletion of more than 10 rows if the specified column contains duplicate values.
USE AdventureWorks2012;
GO
DELETE FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetailID IN
(SELECT TOP 10 PurchaseOrderDetailID
FROM Purchasing.PurchaseOrderDetail
ORDER BY DueDate ASC);
GO
Deleting Rows From a Remote Table
Examples in this section demonstrate how to delete rows from a remote table by using a linked server or a rowset function to reference the remote table. A remote table exists on a different server or instance of SQL Server.
A. Deleting data from a remote table by using a linked server
The following example deletes rows from a remote table. The example begins by creating a link to the remote data source by using sp_addlinkedserver. The linked server name, MyLinkServer, is then specified as part of the four-part object name in the form server.catalog.schema.object.
USE master;
GO
-- Create a link to the remote data source.
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.
EXEC sp_addlinkedserver @server = N'MyLinkServer',
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'server_name',
@catalog = N'AdventureWorks2012';
GO
-- Specify the remote data source using a four-part name -- in the form linked_server.catalog.schema.object. DELETE MyLinkServer.AdventureWorks2012.HumanResources.Department WHERE DepartmentID > 16; GO
B. Deleting data from a remote table by using the OPENQUERY function
The following example deletes rows from a remote table by specifying the OPENQUERY rowset function. The linked server name created in the previous example is used in this example.
DELETE OPENQUERY (MyLinkServer, 'SELECT Name, GroupName FROM AdventureWorks2012.HumanResources.Department WHERE DepartmentID = 18'); GO
C. Deleting data from a remote table by using the OPENDATASOURCE function
The following example deletes rows from a remote table by specifying the OPENDATASOURCE rowset function. Specify a valid server name for the data source by using the format server_name or server_name\instance_name.
DELETE FROM OPENDATASOURCE('SQLNCLI',
'Data Source= <server_name>; Integrated Security=SSPI')
.AdventureWorks2012.HumanResources.Department
WHERE DepartmentID = 17;'
Capturing the results of the DELETE statement
A. Using DELETE with the OUTPUT clause
The following example shows how to save the results of a DELETE statement to a table variable.
USE AdventureWorks2012; GO DELETE Sales.ShoppingCartItem OUTPUT DELETED.* WHERE ShoppingCartID = 20621; --Verify the rows in the table matching the WHERE clause have been deleted. SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621; GO
B. Using OUTPUT with <from_table_name> in a DELETE statement
The following example deletes rows in the ProductProductPhoto table based on search criteria defined in the FROM clause of the DELETE statement. The OUTPUT clause returns columns from the table being deleted, DELETED.ProductID, DELETED.ProductPhotoID, and columns from the Product table. This is used in the FROM clause to specify the rows to delete.
USE AdventureWorks2012;
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
