DELETE (Transact-SQL)
Removes one or more rows from a table or view.
Syntax
DELETE
[ FROM ]
{ <object> | <table_variable> }
[ WHERE ]
[ OPTION ( [ ,...n ] ) ]
<object> ::=
[ database_name . [ schema_name ] . ]{ table_name | view_name }
<table_variable> ::=
<@table_variable_name>
Description
The DELETE statement is used to remove rows from a table. You can delete specific rows by using a WHERE clause or delete all rows by omitting the WHERE clause.
Important
When you delete rows from a table, all rows that match the criteria are removed. If you want to delete all rows from a table, consider using TRUNCATE TABLE, which is more efficient and uses fewer transaction log resources.
Arguments
Parameters
- FROM
Optional keyword that can be used to specify the target table or view. - <object> | <table_variable>
Specifies the table or view from which to delete rows. You can also specify a table variable. - WHERE <expression>
Optional clause that specifies the condition for deleting rows. Only rows that satisfy the condition are deleted. If the WHERE clause is omitted, all rows are deleted. - OPTION ( <query_hint> [ ,...n ] )
Optional clause that specifies query hints.
Basic Syntax Example
DELETE FROM Production.Product
WHERE ListPrice > '100.00';
Deleting All Rows Example
DELETE FROM Sales.SalesOrderDetail;
Note
Deleting all rows from a table using DELETE without a WHERE clause can be a resource-intensive operation, especially for large tables. It logs each row deletion. For faster and more efficient deletion of all rows, use TRUNCATE TABLE.
Permissions
Requires DELETE permission on the table or view.