DELETE vs TRUNCATE behavior in SQL

Introduction

We use DELETE and TRUNCATE to remove data from tables, but they work differently. Knowing how helps keep your data safe and your database fast.

When you want to remove specific rows from a table based on a condition.

When you want to quickly remove all rows from a table without logging each row deletion.

When you need to keep the table structure but clear its data.

When you want to be able to undo the deletion using a transaction.

When you want to reset the table's identity counter after removing all data.

Syntax

SQL

DELETE FROM table_name WHERE condition;

TRUNCATE TABLE table_name;

DELETE removes rows one by one and can use a WHERE clause to filter rows.

TRUNCATE removes all rows quickly and cannot use a WHERE clause.

Examples

Deletes only employees who work in the Sales department.

SQL

DELETE FROM employees WHERE department = 'Sales';

Removes all rows from the employees table quickly.

SQL

TRUNCATE TABLE employees;

Deletes all rows from orders but logs each deletion and can be rolled back.

SQL

DELETE FROM orders;

Sample Program

This example first deletes the row where name is 'Bob', then shows remaining rows. Next, it truncates the table to remove all rows and shows that the table is empty.

SQL

CREATE TABLE test_table (id INT IDENTITY(1,1), name VARCHAR(20));
INSERT INTO test_table (name) VALUES ('Alice'), ('Bob'), ('Charlie');

-- Delete one row
DELETE FROM test_table WHERE name = 'Bob';
SELECT * FROM test_table;

-- Truncate table
TRUNCATE TABLE test_table;
SELECT * FROM test_table;

Important Notes

DELETE operations can be rolled back if inside a transaction; TRUNCATE usually cannot.

TRUNCATE resets identity counters (auto-increment numbers), DELETE does not.

TRUNCATE is faster for removing all rows because it does not log individual row deletions.

Summary

DELETE removes rows one by one and can filter with WHERE.

TRUNCATE removes all rows quickly without logging each row.

Use DELETE for selective removal and TRUNCATE for fast full table clearing.