DELETE (Transact-SQL)
Removes rows from a table or view.
[ WITH <common_table_expression> [ ,...n ] ]
DELETE
[ TOP ( expression ) [ PERCENT ] ]
[ FROM ]
{ <object> | rowset_function_limited
[ WITH ( <table_hint_limited> [ ...n ] ) ]
}
[ <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
}
DELETE can be used in the body of a user-defined function if the object modified is a table variable.
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.
The setting of the SET ROWCOUNT option is ignored for DELETE statements against remote tables and local and remote partitioned views.
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Do not use SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. We recommend that you use the TOP clause instead.
If you want to delete all the rows in a table, use the DELETE statement without specifying a WHERE clause, or use TRUNCATE TABLE. TRUNCATE TABLE is faster than DELETE and uses fewer system and transaction log resources.
Deleting Rows from a Heap
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.
For more information about locking, see Locking in the Database Engine.
Using an INSTEAD OF Trigger on DELETE Actions
When an INSTEAD OF trigger is defined on DELETE actions against a table or view, the trigger executes instead of the DELETE statement. Earlier versions of SQL Server only support AFTER triggers on DELETE and other data modification statements. 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).
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.
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 AdventureWorks2008R2; GO DELETE FROM Sales.SalesPersonQuotaHistory; GO
B. Using DELETE on 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 AdventureWorks2008R2; GO DELETE FROM Production.ProductCostHistory WHERE StandardCost > 1000.00; GO
C. Using DELETE on the current row of a cursor
The following example deletes a single row from the EmployeePayHistory table using a cursor named complex_cursor. The delete operation affects only the single row currently fetched from the cursor.
USE AdventureWorks2008R2;
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
D. Using DELETE based on a subquery and using the Transact-SQL extension
The following example shows the Transact-SQL extension used to delete records from a base table that is based on a join or correlated subquery. The first DELETE statement shows the ISO-compatible subquery solution, and the second DELETE statement shows the Transact-SQL extension. Both queries remove rows from the SalesPersonQuotaHistory table based on the year-to-date sales stored in the SalesPerson table.
-- SQL-2003 Standard subquery
USE AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesYTD > 2500000.00);
GO
-- Transact-SQL extension USE AdventureWorks2008R2; 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
E. Using DELETE with the TOP clause
The following example deletes 2.5 percent of the rows (27 rows) in the ProductInventory table.
USE AdventureWorks2008R2; GO DELETE TOP (2.5) PERCENT FROM Production.ProductInventory; GO
F. Using DELETE with the OUTPUT clause
The following example shows how to save the results of a DELETE statement into a table variable.
USE AdventureWorks2008R2; 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
G. 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 AdventureWorks2008R2;
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
