SQL Truncate Statement

TRUNCATE command is used to remove the existing records from the table, it works the same as DELETE command.

Syntax:-

TRUNCATE TABLE table_name

Difference between DELETE and TRUNCATE Command

DELETE:- When you delete the records from the table then records will be removed but the space of records will exist in the table.

TRUNCATE:-When you truncate the records from the table then records will be removed with exists space.

Example:-
Suppose you have two records in the employees table

 SELECT * FROM employees

Output:-

+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | Virat      | Kohli     | virat@abc.com  | Delhi      |
|  2 | rohit      | Sharma    | rohit@abc.com  | Mumbai     |
+----+------------+-----------+----------------+------------+

Note:- Where id is primary key with auto increment.

Example:-

FIRST CASE:- Now, You want to delete the record from DELETE Command

DELETE FROM employees

Now, check the employees table

SELECT * FROM employees

Output:- returned an empty result set

Now, Add new record into employees table

INSERT INTO employees(first_name,last_name,email,address) 
VALUES ("Sachin","Tendulkar","sachin@abc.com","Mumbai");

Now, check the employees table

SELECT * FROM employees

+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  3 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     |
+----+------------+-----------+----------------+------------+

So, This output is showing that after deleting the records from the table, space will exist so that is the reason id is started from 3.

SECOND CASE:- Now use TRUNCATE command to delete the records.

TRUNCATE TABLE employees

Now, No one record will be shown

Add new record into employees table

INSERT INTO employees(first_name,last_name,email,address) 
VALUES ("Sourav","Ganguly","sourav@abc.com","Calcutta");

Now, check the employees table

SELECT * FROM employees

+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | Sourav     | Ganguly   | sourav@abc.com | Calcutta   |
+----+------------+-----------+----------------+------------+

So, This output is showing that after deleting the records through TRUNCATE Command then space also deleted so that is the reason id is started from 1.

SQL Truncate Statement – Interview Questions

SQL Truncate Statement – Objective Questions (MCQs)

Related SQL Truncate Statement Topics