Which of the following sql statements deletes an object?
Skip to main content This browser is no longer supported. Show
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. DELETE (Transact-SQL)
In this articleApplies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)Removes one or more rows from a table or view in SQL Server. Transact-SQL Syntax ConventionsSyntax
ArgumentsWITH 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 ] FROM table_alias server_name 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 schema_name table_or_view_name A table variable, within its scope, also can be used as a table source in a DELETE statement. The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view definition. For more information about updatable views, see CREATE VIEW (Transact-SQL). rowset_function_limited Either the OPENQUERY or OPENROWSET function, subject to provider capabilities. WITH (
FROM table_source 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 There are two forms of delete operations based on what is specified in the WHERE clause:
CURRENT OF GLOBAL cursor_name cursor_variable_name OPTION ( Best PracticesTo delete all the rows in a table, use Use the Error HandlingYou can implement error handling for the DELETE statement by specifying the statement in a The 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 InteroperabilityDELETE 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
Limitations and RestrictionsWhen
Locking BehaviorBy default, a With an intent exclusive (IX) lock, no other transactions can modify the same set of 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.
Note Empty pages can be removed from a heap at any time by using the Logging BehaviorThe DELETE statement is always fully logged. SecurityPermissions
DELETE permissions default to members of the Examples
Basic SyntaxExamples in this section demonstrate the basic functionality of the DELETE statement using the minimum required syntax. A. Using DELETE with no WHERE clauseThe following example deletes all rows from the
Limiting the Rows DeletedExamples in this section demonstrate how to limit the number of rows that will be deleted. B. Using the WHERE clause to delete a set of rowsThe following example deletes all rows from the
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
C. Using a cursor to determine the row to deleteThe following example deletes a single row from the
D. Using joins and subqueries to data in one table to delete rows in another tableThe following examples show two ways to delete rows in one table based on data in another table. In both examples, rows from the
E. Using TOP to limit the number of rows deletedWhen 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
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
Deleting Rows From a Remote TableExamples 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. Applies to: SQL Server 2008 and later. F. Deleting data from a remote table by using a linked serverThe 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,
G. Deleting data from a remote table by using the OPENQUERY functionThe 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.
H. Deleting data from a remote table by using the OPENDATASOURCE functionThe 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.
Capturing the results of the DELETE statementI. Using DELETE with the OUTPUT clauseThe following example shows how to save the results of a
J. Using OUTPUT with |