Delete Table Row Using SQL Server

Over time, databases can become very large, leading to deteriorating performance. Periodically removing obsolete or inactive records can improve performance and reduce storage requirements by decreasing the number of rows to sort or search through. Reducing database size also frees up disk space that might be needed for other purposes, such as swap space^[1]. Caution should be exercised when deleting data. It's a good practice to archive data before deleting it from a production database^[3].

Use the DELETE statement to remove rows from a SQL database. The syntax^[2] is similar to the SELECT statement, but you do not specify column names since the entire row is deleted. The following DELETE statement will remove all rows from the BookTable table:

DELETE FROM BookTable

This demonstrates how easily an entire table's data can be removed! For a safer, more practical example, consider the book Maintaining Your Moped, which has had poor sales and will no longer be carried by VirtualBookShelf.com. The following diagrams show how the DELETE statement removes only this record from BookTable:

DELETE FROM BookTable
WHERE Title = 'Maintaining Your Moped'

To practice, verify a row exists before deleting it. For example, to check for a test row in the Stores table:

SELECT *
FROM Stores
WHERE StoreCode = 'TEST';

If the row exists, you can safely delete it using a DELETE statement with a similar WHERE clause. The next lesson wraps up this module with a quiz to test your knowledge of basic SQL statements.

[1] swap space: Disk space used by the operating system to temporarily store information removed from main memory.

[2] SQL syntax: Most database actions are performed with SQL statements.

[3] production database: A database used in a live production environment.