Share via

  • Article

Deleting Rows by Using DELETE

The DELETE statement removes one or more rows in a table or view.

A simplified form of the DELETE syntax is:

DELETE table_or_view

FROM table_sources

WHERE search_condition

The parameter table_or_view names a table or view from which the rows are to be deleted. All rows in table_or_view that meet the qualifications of the WHERE search condition are deleted. If a WHERE clause is not specified, all the rows in table_or_view are deleted. The FROM clause specifies additional tables or views and join conditions that can be used by the predicates in the WHERE clause search condition to qualify the rows to be deleted from table_or_view. Rows are not deleted from the tables named in the FROM clause, only from the table named in table_or_view.

Any table that has all rows removed remains in the database. The DELETE statement deletes only rows from the table; the table must be removed from the database by using the DROP TABLE statement.

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 a shared 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.

Examples

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

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

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