TRUNCATE FAQS

1. What is the difference between TRUNCATE and DELETE?

• TRUNCATE:
• Removes all rows from a table, but does not log individual row deletions.
• Cannot be rolled back once committed (since it’s a DDL operation).
• Does not fire triggers.
• Is faster than DELETE for large tables.
• Cannot be used with a WHERE clause.
• DELETE:
• Removes rows from a table based on a condition (WHERE clause).
• Each row deletion is logged and can be rolled back if in a transaction (since it’s a DML operation).
• Fires triggers.
• Slower than TRUNCATE for large tables.

 

2. Can TRUNCATE be rolled back?

• No, TRUNCATE is a DDL operation and cannot be rolled back once it is committed.
• However, if it is done inside a transaction (and before being committed), it will be rolled back, but the operation itself doesn’t support rolling back once committed.

 

3. Can TRUNCATE be used with a WHERE clause?

No, TRUNCATE removes all rows from the table, and you cannot specify a WHERE clause to filter rows like in the DELETE statement. To remove specific rows, you must use DELETE with a WHERE condition.

 

4. Does TRUNCATE remove indexes, constraints, or table structure?

No, TRUNCATE only removes the data from the table but keeps the table structure, indexes, and constraints intact. You can continue using the table after truncating.

 

5. Can TRUNCATE be used on partitioned tables?

Yes, TRUNCATE can be used on partitioned tables. You can truncate an entire partition or the whole table:

TRUNCATE TABLE table_name PARTITION partition_name;

 

6. Does TRUNCATE free up space in the database?

While TRUNCATE frees up space by deallocating the data pages used by the table, it does not shrink the table physically. To reclaim space on disk, you can use the ALTER TABLE command with DEALLOCATE UNUSED:

ALTER TABLE table_name DEALLOCATE UNUSED;

 

7. Can TRUNCATE be used with tables that have foreign key constraints?

No, TRUNCATE cannot be executed on a table that has foreign key constraints unless those constraints are temporarily disabled. To truncate the table, you would either need to remove or disable the foreign key constraints first, or use cascading deletes.

 

8. Is TRUNCATE faster than DELETE?

Yes, TRUNCATE is significantly faster than DELETE because it does not scan the table or log individual row deletions. It just deallocates the space used by the data, making it ideal for clearing out large tables quickly.

 

9. Will TRUNCATE affect triggers?

No, TRUNCATE does not fire triggers that would normally be fired by a DELETE statement. If you need to capture changes or actions through triggers, DELETE would be the better option.

 

10. Can I TRUNCATE a table if it has active indexes?

Yes, you can TRUNCATE a table regardless of whether it has indexes or not. The TRUNCATE operation will not drop or modify any indexes on the table. However, after truncating, you may need to rebuild or reorganize indexes if necessary.

 

11. What happens to the AUTO_INCREMENT value or sequence after a TRUNCATE?

If the table has an auto-incrementing column (e.g., using sequences), TRUNCATE will reset the sequence for the table. This means that the next inserted row will start with the initial value for the sequence (if there is one). However, this behavior can vary depending on the database configuration.

 

12. Can TRUNCATE be used on a view or materialized view?

No, you cannot TRUNCATE a view directly, as views are just virtual tables based on the result of a query. TRUNCATE can only be used on actual tables. However, if you're dealing with a materialized view, it depends on whether it is set up to allow modifications to the base tables. Typically, you'll need to refresh or delete from the base tables that the materialized view is based on.

 

13. What happens to the table’s high-water mark after a TRUNCATE?

After a TRUNCATE, the high-water mark (the point that defines the maximum number of blocks used by the table) is reset. This means that space is made available for future inserts, and the table will not have unused blocks from previous data.

 

14. Is TRUNCATE allowed on a table with triggers?

Yes, TRUNCATE can be executed on a table with triggers, but, as mentioned earlier, it will not fire any DELETE triggers. If your use case requires triggers to be fired, you should use DELETE instead.

 

15. What happens if there are active transactions when TRUNCATE is executed?

If a TRUNCATE is executed and there are active transactions, it will lock the table and commit the operation immediately. Since TRUNCATE is a DDL operation, it does not require a commit to take effect.