Databases Reference
In-Depth Information
The command does not support any addition of optional keywords or para-
meters, making it very simple to use. Keep in mind, however, that constraints
or other restrictions placed on a table or individual columns could still prevent
you from deleting rows from the table.
The table or view name is required. The FROM keyword is optional with
some DBMSs, including SQL Server, but required by others. Some DBMSs
require a WHERE clause and search condition. However, SQL Server does not,
which could lead to a problem. If you run DELETE without a search condi-
tion, all rows are deleted from the table. For example, consider the following
statement:
DELETE SALESPERSON
That statement would delete all rows from the SALESPERSON table. Once
the change is committed, permanently written to the table, the only way to
retrieve the rows would be either to recover from backup or manually reenter
the information. However, reentering won't work if the table has identity
columns because deleting all rows does not revert the identity back to its seed
value. That means that all rows would have new identity values.
It's more likely that you would want to delete one row, or a subset of the
rows, from the table. In an earlier example, you added a salesperson with a
SPNUM value of 427 to SALESPERSON. To delete this person, you could
run:
DELETE SALESPERSON WHERE SPNUM=427
Microsoft SQL Server does support a statement that will both delete all rows
and reset the table's identity column, if it has one, to its seed value. This is a
nonstandard command and is not always included as a DML statement. You
might find it classified as a DDL statement, or as neither. This is the TRUNCATE
TABLE statement, using the syntax:
TRUNCATE TABLE
table_name
The potential problem is that this runs as a
non-logged operation.
Trans-
action logging is beyond the scope of this chapter, but in brief, a non-logged
operation provides a way of rolling back changes as if they were never run. That
means that the change isn't written to the transaction log, but instead is made
directly to the database. SQL Server 2005 changed the description to say that
the command is
minimally logged,
which means that the fact that the operation
ran is logged, but not its effect on the database or its data. Even if the command
is run from within a transaction, which normally lets you roll-back your actions
if caught early enough in the process, there is no way to completely reverse the
result other than by restoring the table from a recent backup. TRUNCATE TABLE
is not a standard ANSI SQL command.
