SQL - TRUNCATE vs DELETE vs DROP | Application Architect

Key Insights

TRUNCATE removes all rows faster than DELETE by deallocating data pages without logging individual row deletions, but cannot be rolled back in most databases and bypasses triggers
DELETE is a DML operation that logs each row removal, fires triggers, and can be filtered with WHERE clauses, making it suitable for selective or auditable deletions
DROP removes the entire table structure and data permanently, requiring table recreation and permission reassignment, while TRUNCATE and DELETE preserve the table schema

Understanding the Core Differences

SQL provides three distinct commands for removing data: TRUNCATE, DELETE, and DROP. Each serves different purposes and has unique characteristics that impact performance, recoverability, and side effects.

DELETE is a Data Manipulation Language (DML) operation that removes rows based on conditions. TRUNCATE is technically DDL (Data Definition Language) that removes all rows by deallocating data pages. DROP is DDL that removes the entire table object from the database.

DELETE: Selective Row Removal

DELETE removes rows one at a time and logs each deletion in the transaction log. This makes it slower but provides granular control and full recoverability.

-- Delete specific rows
DELETE FROM orders 
WHERE order_date < '2023-01-01';

-- Delete all rows (still logs each one)
DELETE FROM orders;

-- Delete with JOIN
DELETE o 
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.status = 'inactive';

-- Check rows affected
SELECT @@ROWCOUNT; -- SQL Server
-- or
SELECT ROW_COUNT(); -- MySQL

DELETE respects foreign key constraints and fires triggers, making it the safest option when data integrity and business logic must be enforced:

CREATE TRIGGER audit_order_deletion
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, action, record_id, deleted_at)
    VALUES ('orders', 'DELETE', OLD.order_id, NOW());
END;

-- This DELETE will fire the trigger
DELETE FROM orders WHERE order_id = 12345;

DELETE operations can be rolled back within a transaction:

BEGIN TRANSACTION;

DELETE FROM orders WHERE customer_id = 100;
-- Review what was deleted
SELECT * FROM audit_log WHERE record_id = 100;

-- Undo if needed
ROLLBACK;
-- or commit
COMMIT;

TRUNCATE: Fast Table Reset

TRUNCATE removes all rows by deallocating entire data pages rather than deleting rows individually. This makes it significantly faster for large tables but removes flexibility.

-- Remove all rows from table
TRUNCATE TABLE orders;

-- Cannot use WHERE clause - syntax error
-- TRUNCATE TABLE orders WHERE order_date < '2023-01-01'; -- ERROR

-- Resets identity columns
CREATE TABLE test_sequence (
    id INT IDENTITY(1,1),
    value VARCHAR(50)
);

INSERT INTO test_sequence (value) VALUES ('First'), ('Second');
-- id values: 1, 2

TRUNCATE TABLE test_sequence;

INSERT INTO test_sequence (value) VALUES ('Third');
-- id value: 1 (reset)

TRUNCATE has important limitations with foreign keys:

-- This will fail if child records exist
CREATE TABLE parent_table (
    id INT PRIMARY KEY
);

CREATE TABLE child_table (
    id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);

INSERT INTO parent_table VALUES (1);
INSERT INTO child_table VALUES (1, 1);

-- ERROR: Cannot truncate table 'parent_table' because it is being 
-- referenced by a FOREIGN KEY constraint
TRUNCATE TABLE parent_table;

-- Must delete from child first
DELETE FROM child_table;
TRUNCATE TABLE parent_table; -- Now succeeds

TRUNCATE doesn’t fire DELETE triggers:

CREATE TRIGGER log_deletions
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO deletion_log VALUES (OLD.order_id, NOW());
END;

DELETE FROM orders WHERE order_id = 1; -- Fires trigger
TRUNCATE TABLE orders; -- Does NOT fire trigger

DROP: Complete Table Removal

DROP removes the entire table structure, including data, indexes, triggers, constraints, and permissions. This is irreversible without backups.

-- Remove table completely
DROP TABLE orders;

-- Conditional drop (SQL Server 2016+, PostgreSQL, MySQL)
DROP TABLE IF EXISTS orders;

-- Drop multiple tables
DROP TABLE orders, order_items, order_history;

-- After DROP, table doesn't exist
SELECT * FROM orders; -- ERROR: Table 'orders' doesn't exist

DROP removes all associated objects:

-- Create table with constraints and indexes
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    category_id INT,
    INDEX idx_category (category_id),
    CONSTRAINT chk_price CHECK (price > 0)
);

CREATE TRIGGER update_inventory
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    -- trigger logic
END;

-- DROP removes everything
DROP TABLE products;
-- Removes: table, primary key, index, check constraint, trigger

-- Must recreate from scratch
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

Performance Comparison

The performance differences become significant with large datasets:

-- Setup test table
CREATE TABLE performance_test (
    id INT PRIMARY KEY,
    data VARCHAR(1000)
);

-- Insert 1 million rows
INSERT INTO performance_test (id, data)
SELECT 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
    REPLICATE('x', 1000)
FROM master..spt_values a
CROSS JOIN master..spt_values b;

-- Test DELETE (slowest - logs each row)
DECLARE @start DATETIME = GETDATE();
DELETE FROM performance_test;
SELECT DATEDIFF(MILLISECOND, @start, GETDATE()) as 'DELETE ms';
-- Result: ~45000 ms

-- Recreate table
-- Test TRUNCATE (fastest)
DECLARE @start DATETIME = GETDATE();
TRUNCATE TABLE performance_test;
SELECT DATEDIFF(MILLISECOND, @start, GETDATE()) as 'TRUNCATE ms';
-- Result: ~50 ms

-- Test DROP (fast, but removes structure)
DECLARE @start DATETIME = GETDATE();
DROP TABLE performance_test;
SELECT DATEDIFF(MILLISECOND, @start, GETDATE()) as 'DROP ms';
-- Result: ~30 ms

Transaction Log Impact

DELETE generates significant transaction log activity:

-- Monitor log space usage
DBCC SQLPERF(LOGSPACE);

-- DELETE logs every row
BEGIN TRANSACTION;
DELETE FROM large_table; -- Generates massive log entries
-- Transaction log grows substantially
COMMIT;

-- TRUNCATE uses minimal logging
BEGIN TRANSACTION;
TRUNCATE TABLE large_table; -- Minimal log entries
COMMIT;

Choosing the Right Command

Use DELETE when you need:

Conditional row removal with WHERE clause
Trigger execution for business logic
Auditing and compliance requirements
Transaction rollback capability
Foreign key constraint enforcement

-- Audit-compliant deletion
BEGIN TRANSACTION;
DELETE FROM customer_data 
WHERE last_access_date < DATEADD(YEAR, -7, GETDATE())
AND gdpr_consent = 0;
-- Audit trail created via triggers
COMMIT;

Use TRUNCATE when you need:

Fast removal of all rows
Identity column reset
Minimal transaction log usage
No foreign key dependencies

-- Clear staging table between ETL runs
TRUNCATE TABLE staging_imports;
-- Fast, resets identity, ready for new data

Use DROP when you need:

Complete table removal
Schema changes requiring table recreation
Decommissioning old tables

-- Remove deprecated table
DROP TABLE legacy_customer_data;
-- Frees all storage and removes all metadata

Database-Specific Behavior

PostgreSQL allows TRUNCATE with CASCADE to handle foreign keys:

-- PostgreSQL only
TRUNCATE TABLE parent_table CASCADE;
-- Automatically truncates referencing tables

MySQL TRUNCATE cannot be rolled back even in transactions:

-- MySQL
START TRANSACTION;
TRUNCATE TABLE orders; -- Cannot be undone
ROLLBACK; -- Has no effect on TRUNCATE

SQL Server allows DELETE with TOP for batched deletions:

-- SQL Server - delete in batches
WHILE 1=1
BEGIN
    DELETE TOP (10000) FROM large_table
    WHERE created_date < '2023-01-01';
    
    IF @@ROWCOUNT < 10000 BREAK;
    WAITFOR DELAY '00:00:01'; -- Prevent log overflow
END

Understanding these three commands ensures you choose the right tool for data removal tasks, balancing performance, safety, and functional requirements.