How to Delete Rows from a Table in SQL Server
When managing data in a SQL Server database, there are times you need to remove unwanted or obsolete records from a table. SQL Server provides powerful commands to help you delete data safely and efficiently. This article explains different ways to delete rows from a table using the DELETE and TRUNCATE statements, along with examples and safety tips.
1. Delete a Specific Row by ID (or Primary Key)
When you want to delete a specific row identified by a unique column (like a primary key), use the DELETE statement with a WHERE clause:
DELETE FROM SubsectionMaster
WHERE SubsectionID = 5; -- Deletes the row where SubsectionID is 5
DELETE FROM PageNameMaster
WHERE PageID = 10; -- Deletes the row where PageID is 10
Use this method when deleting one or a few known records.
2. Delete Rows Matching a Specific Condition
To delete multiple rows based on a condition (like a name match), use the WHERE clause with the desired condition:
DELETE FROM SubsectionMaster
WHERE SubsectionName = 'Test';
DELETE FROM PageNameMaster
WHERE PageName = 'TestPage';
Be cautious: If multiple rows match the condition, all of them will be deleted.
3. Delete All Rows from a Table
To clear all data from a table, you can either use the DELETE statement without a WHERE clause or use the TRUNCATE TABLE command:
Option 1: Delete all rows
DELETE FROM SubsectionMaster;
DELETE FROM PageNameMaster;
Option 2: Truncate all rows (faster and resets identity values)
TRUNCATE TABLE SubsectionMaster;
TRUNCATE TABLE PageNameMaster;
TRUNCATE is faster but:
- Cannot be used if there are foreign key constraints.
- Does not log individual row deletions, unlike
DELETE.
4. Backup Before Deleting (Best Practice)
Before performing any delete operation—especially on important data—always take a backup of the table:
SELECT * INTO SubsectionMaster_Backup FROM SubsectionMaster;
SELECT * INTO PageNameMaster_Backup FROM PageNameMaster;
This creates a full copy of the table (structure and data) in a new backup table.
Tips to Avoid Accidental Data Loss
- Always use
WHERE in DELETE unless you intentionally want to delete everything.
- Run a
SELECT query first to preview which rows will be deleted.
- Back up critical data regularly.
- For large delete operations, consider batch deletion to avoid locking issues.
Summary
| Task |
SQL Command |
| Delete a specific row |
DELETE FROM table WHERE ID = value; |
| Delete rows with condition |
DELETE FROM table WHERE column = value; |
| Delete all rows |
DELETE FROM table; or TRUNCATE TABLE table; |
| Backup before delete |
SELECT * INTO table_Backup FROM table; |