Difference between DELETE and TRUNCATE

DELETE and TRUNCATE are SQL commands are used to remove data from tables, but they differ in behavior and performance. They are used to:

• DELETE remove selected rows using a WHERE clause and allow rollback in transactions.
• TRUNCATE remove all rows from a table quickly without using a WHERE clause.

DELETE Command in SQL

The DELETE command in SQL is part of the DML (Data Manipulation Language) category and is used to remove specific rows from a table based on a condition. We can delete all rows or filter which rows to delete by using a WHERE clause.

Syntax

DELETE FROM TableName 
WHERE condition; 

Example of DELETE Command

Let’s understand this with a simple example: first, we will create an Employee table, and then we will perform the DELETE operation on it.

Employee Table

CREATE TABLE Employee (
    Emp_id INT, 
    name VARCHAR(20), 
    country VARCHAR(20),
    Salary INT
);

INSERT INTO Employee (Emp_id, name, country, Salary)
VALUES 
    (101, 'Michael', 'USA', 60000),
    (103, 'James', 'Canada', 70000),
    (104, 'William', 'USA', 100000),
    (102, 'John', 'USA', 40000),
    (105, 'Robert', 'USA', 50000),
    (106, 'David', 'USA', 30000);

SELECT * FROM Employee;                                                                                     

Output

We must now create a query to remove the last entry with the value 106 for the Emp_id.

Query:

Delete from Employee where Emp_id = 106;

Output:

TRUNCATE Command in SQL

TRUNCATE is a DDL command used to delete all rows from a table at once. It does not allow a WHERE clause, so individual rows cannot be deleted.It is faster than the DELETE command because it logs minimal information, and in most cases the data cannot be rolled back after execution.

Syntax

TRUNCATE TABLE  TableName;

Example for TRUNCATE Command

TRUNCATE deletes all rows from a table and is faster than DELETE because it removes the data at once with minimal logging and does not fire triggers.

Query:

TRUNCATE TABLE Employee;

Rollback Example

TRUNCATE can be rolled back only in database systems that allow it inside a transaction. In many systems, TRUNCATE performs an automatic commit, so the operation cannot be rolled back.

Query:

BEGIN TRANSACTION;
   TRUNCATE TABLE Employee;
ROLLBACK TRANSACTION;

DELETE VS TRUNCATE

The DELETE and TRUNCATE commands are used to remove data from a table, but they differ in speed, usage, and functionality. The main differences are as follows: