The TRUNCATE TABLE statement is used in SQL to quickly remove all rows from a table while preserving the table structure, indexes, and constraints. This operation is typically faster than using the DELETE statement without a WHERE clause because it does not log individual row deletions.
Here’s how to use the TRUNCATE TABLE statement:
TRUNCATE TABLE table_name;
Key Points:
1. Syntax: Replace table_name with the name of the table from which you want to remove all rows.
2. Speed: TRUNCATE TABLE is generally faster than DELETE because it does not generate a transaction log for each row deleted. Instead, it deallocates entire pages of data.
3. No WHERE Clause: Unlike DELETE, the TRUNCATE TABLE statement does not allow for a WHERE clause. You cannot selectively remove rows; it removes all of them.
4. No Triggers: Generally, TRUNCATE TABLE does not fire any triggers that you may have set up on the table, as it does not operate on a row-by-row basis.
5. Cannot be used with Foreign Keys: You cannot truncate a table that is referenced by a foreign key from another table. You must first remove the foreign key constraint before truncating.
6. Resets Identity Columns: If your table contains an identity column, using TRUNCATE TABLE will reset the identity counter to its seed value.
Example:
If you have a table named Employees, and you want to remove all rows from it, you would execute:
TRUNCATE TABLE Employees;
Caution:
- Use TRUNCATE TABLE with care, as it cannot be rolled back if executed outside of a transaction. Once you truncate a table, all data is permanently removed. If you need the ability to undo your changes, consider using DELETE with a WHERE clause or wrapping your TRUNCATE statement in a transaction in databases that support this.
Overall, TRUNCATE TABLE is an efficient way to clear a table when you are sure you want to remove all data from it.