TSQLTutorial: Deleting Data

The SQL DELETE command is used to remove data from tables. To delete all the rows in a table at once,

Use this syntax:

DELETE FROM customers

Guru’s Guide to Transact-SQL

Similarly to INSERT, the FROM keyword is optional. Like UPDATE, DELETE can optionally include a WHERE

clause to qualify the rows it removes. Here's an example:

DELETE FROM customers

WHERE LastName<>'Doe'

SQL Server provides a quicker, more brute-force command for quickly emptying a table. It's similar to the

dBASE ZAP command and looks like this:

TRUNCATE TABLE customers

TRUNCATE TABLE empties a table without logging row deletions in the transaction log. It can't be used

with tables referenced by FOREIGN KEY constraints, and it invalidates the transaction log for the entire

database.

Once the transaction log has been invalidated, it can't be backed up until the next full database backup.

TRUNCATE TABLE also circumvents the triggers defined on a table, so DELETE triggers don't re, even

though, technically speaking, rows are being deleted from the table. (See Chapter4, "DDL Insights," for

more information.)

TSQL Tutorial...