Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

III.7.4. Removing Data from Your Database

The DELETE statement is used to remove rows from a table or view.

Some general rules and guidelines to follow when removing data with the DELETE statement are

• Foreign keys: If a row is referenced in another table with a foreign key constraint, the DELETE statement will fail. For example, a Sales table might be related to a Customers table by a foreign key in the Sales table referencing a primary key in the Customers table.

  If a customer is deleted from the Customers table, an orphan is left in the Sales table — a sale would point to a non-existent customer. This situation isn't allowed, and the DELETE fails.

• WHERE: The WHERE clause identifies which rows will be deleted. The WHERE clause works the same in a DELETE statement as it does in a SELECT statement. Support for all the same operations works the same in both commands.

  Caveat Coder (Coder Beware!). If the WHERE clause is omitted, all the rows in the table will be deleted. Needless to say, this may ruin your day.

NOTE

If you wish to delete all the rows in a table, the TRUNCATE TABLE command is usually used instead of the DELETE command. TRUNCATE TABLE is quicker and more efficient, although it doesn't log as much information in the transaction log and can't be rolled back.

The basic syntax of the DELETE command is

DELETE FROM tableName
WHERE VALUE = value

Use the following steps to create a populated database and then delete some rows from a table:

1. Launch SQL Server Management Studio ...