04 SQL ‐ TRUNCATE TABLE

In SQL, the TRUNCATE TABLE statement is used to quickly and efficiently remove all rows from a table, effectively deleting all the data in the table while keeping the table structure intact. It is commonly used when you want to delete all records from a table but retain the table's schema, including columns, indexes, and constraints.

Here's the basic syntax for the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name;

• table_name: This is the name of the table from which you want to delete all rows.

Keep in mind the following points when using TRUNCATE TABLE:

1. Unlike the DELETE statement, which removes rows one at a time and allows you to specify a WHERE clause for selective deletions, TRUNCATE TABLE removes all rows from the table without conditions.

2. TRUNCATE TABLE is typically faster and uses fewer system resources than DELETE, especially for large tables, because it doesn't generate individual delete statements for each row.

3. TRUNCATE TABLE is also an atomic operation, which means it can be rolled back if used within a transaction, and it can be a better choice when you need to quickly remove all data from a table but preserve the table's structure.

Here's an example of using TRUNCATE TABLE to remove all data from a table named "employees":

TRUNCATE TABLE employees;

Please be cautious when using TRUNCATE TABLE because it permanently deletes all data from the specified table. Ensure you have the necessary permissions to perform this operation, and make sure to back up your data if needed before truncating a table.