SQL TRUNCATE TABLE

Introduction

In this chapter, we will learn how to use the TRUNCATE TABLE statement in SQL. The TRUNCATE TABLE statement is used to delete all rows from a table quickly and efficiently while preserving the table structure for future use. This chapter will cover the syntax, usage, and key differences between TRUNCATE and DELETE.

What is TRUNCATE TABLE?

The TRUNCATE TABLE statement removes all rows from a table without logging individual row deletions. This makes it faster than the DELETE statement for large tables. However, it does not fire triggers and cannot be rolled back in some databases.

Syntax for TRUNCATE TABLE

Basic Syntax

TRUNCATE TABLE table_name;

• table_name: The name of the table you want to truncate.

Example

Let's assume we have a table named employees:

TRUNCATE TABLE employees;

This command removes all rows from the employees table, but the table structure remains intact.

Example Workflow

Step-by-Step Example

1. Create a Sample Table:

   CREATE TABLE employees (
       id INT PRIMARY KEY AUTO_INCREMENT,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       email VARCHAR(100)
   );
   

2. View the Table Structure:

   Before truncating the table, you can view its structure.

   DESCRIBE employees;
   

   Output

   Field	Type	Null	Key	Default	Extra
   id	int(11)	NO	PRI	NULL	auto_increment
   first_name	varchar(50)	YES		NULL
   last_name	varchar(50)	YES		NULL
   email	varchar(100)	YES	UNI	NULL

3. Insert Sample Data:

   INSERT INTO employees (first_name, last_name, email)
   VALUES ('Ramesh', 'Kumar', 'ramesh.kumar@example.com'),
          ('Sita', 'Patel', 'sita.patel@example.com');
   

4. View the Data:

   SELECT * FROM employees;
   

   Output

   id	first_name	last_name	email
   1	Ramesh	Kumar	ramesh.kumar@example.com
   2	Sita	Patel	sita.patel@example.com

5. Truncate the Table:

   TRUNCATE TABLE employees;
   

6. Verify the Truncation:

   SELECT * FROM employees;
   

   Output

   The table will be empty:

   id	first_name	last_name	email

Differences Between TRUNCATE and DELETE

While both TRUNCATE and DELETE are used to remove data from a table, there are important differences between them:

TRUNCATE

• Speed: Faster because it does not log individual row deletions.
• Rollback: Cannot be rolled back in some databases.
• Triggers: Does not fire triggers.
• Usage: Used when you need to quickly remove all rows from a table.

DELETE

• Speed: Slower because it logs individual row deletions.
• Rollback: Can be rolled back if used within a transaction.
• Triggers: Fires triggers.
• Usage: Used when you need to remove specific rows or when you need to fire triggers.

Example of DELETE

DELETE FROM employees WHERE id = 1;

This command deletes the row with id = 1 from the employees table, and it can be rolled back if used within a transaction.

Conclusion

The TRUNCATE TABLE statement is a powerful and efficient way to quickly remove all rows from a table while preserving its structure. It is faster than the DELETE statement for large tables but comes with some limitations, such as not firing triggers and potential rollback issues. Understanding when to use TRUNCATE versus DELETE is important for effective database management.