DELETE

DELETE removes rows from a table. A simple DELETE statement looks like this:

 DELETE Products WHERE SupplierID = '7'

It knocks supplier 7 from our Products table because the company raised its prices by 30 percent. The full syntax of the DELETE is as follows:

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

As demonstrated by the syntax, you will always need to provide a source table or result set variable from which to delete a row or series of rows; however, the FROM is optional and represents the old ANSI SQL-92 standard syntax. (You use DROP if the object of the delete is the entire table.) You also do not delete columns in the DELETE statement, only rows or a range of rows that meet a certain criterion, such as where all customers are past due by five years. Of course, if you omit the WHERE clause, you could end up with an empty table, which (in a certain way) does what TRUNCATE does (discussed shortly).

Tip 

Use the system function @@ROWCOUNT in all DML statements if you need to return to the client the number of rows that were affected by any of the INSERT, UPDATE, and DELETE statements, or if it is required as a parameter in a procedure.

While the syntax says you can delete rows in a view, the delete goes down to the actual base table. But if the view is derived from two or more base tables, you would not be able to delete via the view, because there is no way to reference more than one table in the DELETE (from VIEW) statement.

The bullet list that follows here lists the various forms in which DELETE can be used:

• DELETE with no WHERE clause

• DELETE on a set of rows

• DELETE on the current row of a cursor

• DELETE based on a subquery and using the Transact-SQL extension

• DELETE with the TOP clause

• DELETE with the OUTPUT clause

• OUTPUT with from table_name in a DELETE statement

TRUNCATE TABLE

I mentioned TRUNCATE TABLE earlier, but you should know that while both statements can be used to remove all the rows from a table, there are some important differences. TRUNCATE TABLE, for starters, is much faster than DELETE because TRUNCATE TABLE completely deallocates the data pages and any indexes attached to the table. DELETE removes the rows one at a time and maintains the data page even at the instant it is devoid of all rows. Also, every row removal in the DELETE statement is fully logged (and thus recoverable after each row delete transaction), while TRUNCATE TABLE cannot log each row as being removed from the deallocation of the data pages, because the rows are not actually deleted; the data page is just unhinged. However, the transaction logs the TRUNCATE TABLE’s resulting page removal, which also means much less log space is used.

Tip 

You can recover from a TRUNCATE if it is enclosed inside transactions.

Here is an example of the TRUNCATE in action:

 TRUNCATE TABLE Products

Very complex, isn’t it? So complex is it that often you can forget to include the keyword TABLE. Running “TRUNCATE Products” will not work.

While TRUNCATE TABLE provides a handy table row eradicator, there are two places you cannot take it:

• It cannot be used on tables referenced by foreign keys.

• It cannot be used on tables that are required in replication scenarios.