DELETE (Transact-SQL)

Removes rows from a table or view.

Transact-SQL Syntax Conventions

Syntax

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ FROM ] 
        { table_name [ WITH ( <table_hint_limited> [ ...n ] ) ]
        | view_name 
        | rowset_function_limited 
        | table_valued_function
    }
    [ <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 
}

Arguments

• WITH <common_table_expression>
  Specifies the temporary named result set, also known as common table expression, defined within the scope of the DELETE statement. The result set is derived from a SELECT statement.

  Common table expressions can also be used with the SELECT, INSERT, UPDATE, and CREATE VIEW statements. For more information, see WITH common_table_expression (Transact-SQL).

• TOP (expression) [ PERCENT ]
  Specifies the number or percent of random rows that will be deleted. expression can be either a number or a percent of the rows. The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order.

  Parentheses delimiting expression in TOP are required in INSERT, UPDATE, and DELETE statements. For more information, see TOP (Transact-SQL).

• FROM
  Is an optional keyword that can be used between the DELETE keyword and the target table_or_view_name, or rowset_function_limited.

• server_name
  Is the name of the server (using a linked server name or the OPENDATASOURCE function as the server name) on which the table or view is located. If server_name is specified, database_name and schema_name are required.

• database_name
  Is the name of the database.

• schema_name
  Is the name of the schema to which the table or view belongs.

• table
  Is the name of the table from which the rows are to be removed.

  A table variable, within its scope, also can be used as a table source in a DELETE statement.

• WITH ( <table_hint_limited> [... n] )
  Specifies one or more table hints that are allowed for a target table. The WITH keyword and the parentheses are required. NOLOCK and READUNCOMMITTED are not allowed. For more information about table hints, see Table Hint (Transact-SQL).

• view_name
  Is the name of the view from which the rows are to be removed.

  The view referenced by view_name must be updatable and reference exactly one base table in the FROM clause of the view. For more information about updatable views, see CREATE VIEW (Transact-SQL).

• rowset_function_limited
  Is either the OPENQUERY or OPENROWSET function, subject to provider capabilities. For more information about the capabilities required by the provider, see UPDATE and DELETE Requirements for OLE DB Providers.

• table_valued_function
  Can be a table valued function. For more information on table valued functions, see Table-valued User-defined Functions.

• <OUTPUT_Clause>
  Returns deleted rows, or expressions based on them, as part of the DELETE operation. The OUTPUT clause is not supported in any DML statements targeting views or remote tables. For more information, see OUTPUT Clause (Transact-SQL).

• FROM <table_source>
  Specifies an additional FROM clause. This Transact-SQL extension to DELETE allows specifying data from <table_source> and deleting the corresponding rows from the table in the first FROM clause.

  This extension, specifying a join, can be used instead of a subquery in the WHERE clause to identify rows to be removed.

  For more information, see FROM (Transact-SQL).

• WHERE
  Specifies the conditions used to limit the number of rows that are deleted. If a WHERE clause is not supplied, DELETE removes all the rows from the table.

  There are two forms of delete operations based on what is specified in the WHERE clause:

  • Searched deletes specify a search condition to qualify the rows to delete. For example, WHERE column_name = value.
  • Positioned deletes use the CURRENT OF clause to specify a cursor. The delete operation occurs at the current position of the cursor. This can be more accurate than a searched DELETE statement that uses a WHERE search_condition clause to qualify the rows to be deleted. A searched DELETE statement deletes multiple rows if the search condition does not uniquely identify a single row.

• <search_condition>
  Specifies the restricting conditions for the rows to be deleted. There is no limit to the number of predicates that can be included in a search condition. For more information, see Search Condition (Transact-SQL).

• CURRENT OF
  Specifies that the DELETE is performed at the current position of the specified cursor.

• GLOBAL
  Specifies that cursor_name refers to a global cursor.

• cursor_name
  Is the name of the open cursor from which the fetch is made. If both a global and a local cursor with the name cursor_name exist, this argument refers to the global cursor if GLOBAL is specified; otherwise, it refers to the local cursor. The cursor must allow updates.

• cursor_variable_name
  Is the name of a cursor variable. The cursor variable must reference a cursor that allows updates.

• OPTION ( <query_hint> [ ,... n] )
  Are keywords that indicate that optimizer hints are used to customize the way the Database Engine processes the statement. For more information, see Query Hint (Transact-SQL).

Remarks

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.

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 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 Hint (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).

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.

Examples

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 AdventureWorks;
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 AdventureWorks;
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 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. 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 SQL-2003-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 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. Using DELETE with the TOP clause

The following example deletes 2.5 percent of the rows (27 rows) in the ProductInventory table.

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

F. Using DELETE with the OUTPUT clause

The following example deletes all rows in the Sales.ShoppingCartItem table. The clause OUTPUT DELETED.* INTO @MyTableVar specifies that the results of the DELETE statement, that is all columns in the deleted rows, be returned to the @MyTableVartable variable. Two SELECT statements follow that return the values in @MyTableVar and the results of the delete operation in the ShoppingCartItem table.

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.* ;

--Verify all rows in the table have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;
GO

Here are the result sets of the SELECT statements:

ShoppingCartItemID  ShoppingCartID  Quantity  ProductID 
------------------  --------------  --------  ---------
2                   14951           3         862
5                   20621           7         874
4                   20621           4         881

(3 row(s) affected)

Rows in Table 
------------- 
0

(1 row(s) affected)

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

See Also

Reference

CREATE TRIGGER (Transact-SQL)
INSERT (Transact-SQL)
SELECT (Transact-SQL)
TRUNCATE TABLE (Transact-SQL)
UPDATE (Transact-SQL)
WITH common_table_expression (Transact-SQL)

Other Resources

Deleting Data in a Table

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	New content: In the Remarks section, added the section "Deleting Rows from a Heap."