Differenc between Truncate and Delete

The primary difference between the TRUNCATE and DELETE commands in SQL lies in how they function and their use cases. Here’s a detailed comparison:

    1. Basic Purpose
      • DELETE:
        • Deletes specific rows from a table based on a condition. If no condition is specified, all rows in the table are removed.
      • TRUNCATE:
        • Removes all rows from a table without filtering or specifying conditions. It is essentially a fast way to empty a table.
      • DELETE Example:
        • DELETE FROM employees WHERE department = ‘HR’;
      • TRUNCATE Example:
        • TRUNCATE TABLE employees;
    1. Performance
      • DELETE:
        • Slower because it logs each row deletion in the transaction log and can trigger triggers associated with the table.
      • TRUNCATE:
        • Faster because it does not log individual row deletions and does not activate triggers.
    1. Rollback and Transaction Control
      • DELETE:
        • Can be rolled back (reversed) using a ROLLBACK statement if executed within a transaction.
      • TRUNCATE:
        • Cannot be rolled back once executed, as it is usually considered a DDL (Data Definition Language) operation.
    1. Log and Locking Behavior
      • DELETE:
        • Logs each deleted row and holds row-level locks, which can impact performance on large datasets.
      • TRUNCATE:
        • Logs only the deallocation of data pages and holds table-level locks.
    1. Triggers
      • DELETE:
        • Activates any triggers defined on the table.
      • TRUNCATE:
        • Does not activate triggers because it does not operate on individual rows.
    1. Structure and Metadata
      • DELETE:
        • Does not reset identity columns or remove metadata about the table.
      • TRUNCATE:
        • Resets identity columns (if they exist) and may affect related metadata.
    1. Permissions
      • DELETE:
        • Requires DELETE permissions on the table.
      • TRUNCATE:
        • Requires ALTER permissions on the table.
    1. Use case
      • TRUNCATE:
        • Use when you need to quickly remove all rows from a table and reset it, typically during maintenance or testing.

Example Comparison

Assume a table named employees:

    • DELETE Example:
      • DELETE FROM employees WHERE department = ‘HR’;

This removes only rows where the department is ‘HR’.

    • TRUNCATE Example:
      • TRUNCATE TABLE employees;

This removes all rows from the table.