Question

Explain difference between delete and truncate command with example.

Answer 1

Of course. Here is a detailed explanation of the differences between the DELETE and TRUNCATE commands in SQL, which are both used to remove data from a table but operate in fundamentally different ways.

---

Introduction

Both DELETE and TRUNCATE are used to remove rows from a table, but they are designed for different purposes and have different implications for performance, transaction control, and database resources.

• DELETE is a DML (Data Manipulation Language) command.
• TRUNCATE is a DDL (Data Definition Language) command.

This core difference is the source of all their other distinctions.

---

Sample Data for Examples

To illustrate, let's use a simple Products table with a few records. Assume this table has a primary key that auto-increments.

Products Table (Initial State):
| ProductID | ProductName | Category |
| :--- | :--- | :--- |
| 1 | Laptop | Electronics |
| 2 | Mouse | Electronics |
| 3 | T-Shirt | Apparel |
| 4 | Jeans | Apparel |

---

DELETE Command

The DELETE command is used to remove one or more rows from a table based on a condition.

• Operation: It removes rows one by one and records each deletion in the transaction log.

• Syntax:
  `sql
  -- Delete specific rows
  DELETE FROM table_name WHERE condition;

  -- Delete all rows (still one by one)
  DELETE FROM table_name;
  `

• Example: Deleting Specific Rows
  Let's remove all products from the 'Apparel' category.

  `sql
  DELETE FROM Products WHERE Category = 'Apparel';
  `
Products Table (After DELETE):
  | ProductID | ProductName | Category |
  | :--- | :--- | :--- |
  | 1 | Laptop | Electronics |
  | 2 | Mouse | Electronics |

• Example: Deleting All Rows
  If you were to run DELETE FROM Products;, it would remove the 'Laptop' and 'Mouse' rows as well, leaving the table empty.

---

TRUNCATE Command

The TRUNCATE command is used to quickly remove all rows from a table.

• Operation: It deallocates the data pages used by the table, which is a much faster operation than deleting rows individually. It makes minimal use of the transaction log.
• Syntax:
  `sql
  TRUNCATE TABLE table_name;
  `

• Example: Truncating the Table
  Let's remove all data from our original Products table.

  `sql
  TRUNCATE TABLE Products;
  `
Products Table (After TRUNCATE):
  The table is now completely empty. The structure of the table (columns, constraints, etc.) remains, but all data is gone.

---

Key Differences: DELETE vs. TRUNCATE

Here is a summary table highlighting the crucial distinctions:

| Feature | DELETE (DML) | TRUNCATE (DDL) |
| :--- | :--- | :--- |
| Purpose | To remove specific rows (or all rows). | To remove all rows from a table quickly. |
| WHERE Clause | Can use a WHERE clause to filter which rows to delete. | Cannot use a WHERE clause. It always removes all rows. |
| Speed | Slower, as it removes rows one by one and logs each deletion. | Much faster, as it deallocates data pages instead of processing individual rows. |
| Transaction Control| Can be rolled back. Each deleted row is logged, so a ROLLBACK can restore them. | Cannot be rolled back in most databases (like Oracle, SQL Server). It is an auto-committed operation. |
| Triggers | Fires DELETE triggers for each row that is removed. | Does not fire DELETE triggers. |
| Identity Reset | Does not reset the identity (auto-increment) value of a column. If the last ProductID was 4, the next one will be 5. | Resets the identity value of a column back to its starting seed (usually 1). |
| Locking | Places a row-level lock on each row it is deleting. | Places a table-level lock, preventing other sessions from accessing the table while it works. |
| Space Deallocation| May not immediately release the disk space used by the deleted rows. A vacuum or shrink operation might be needed. | Immediately deallocates and releases the disk space used by the table's data. |

Practical Scenario: When to Use Which?

• Use DELETE when:
  You need to remove only a subset of rows from a table.
  You need the operation to be reversible (i.e., you might need to ROLLBACK).
  You need to fire a trigger for each deleted row (e.g., to log the deletion in an audit table).
  You are deleting from a very small table where the performance difference is negligible.

• Use TRUNCATE when:
  You want to completely and permanently empty a large table.
  Performance is critical, and you need the fastest way to delete all data.
  You want to reset the table's identity column back to its starting value.
  You do not need to ROLLBACK the operation. (e.g., clearing out a temporary staging table before loading new data).