ISRO2020-48

Question

Properties of $\text{‘DELETE’}$ and $\text{‘TRUNCATE’}$ commands indicate that

• A. After the execution of $\text{‘TRUNCATE’}$ operation, $\text{COMMIT}$, and $\text{ROLLBACK}$ statements cannot be performed to retrieve the lost data, while $\text{‘DELETE’}$ allow it
• B. After the execution of $\text{‘DELETE’}$ and $\text{‘TRUNCATE’}$ operation retrieval is easily possible for the lost data
• C. After the execution of $\text{‘DELETE’}$ operation, $\text{COMMIT}$ and $\text{ROLLBACK}$ statements can be performed to retrieve the lost data, while $\text{TRUNCATE}$ do not allow it
• D. After the execution of $\text{‘DELETE’}$ and $\text{‘TRUNCATE’}$ operation no retrieval is possible for the lost data

Comments

Aren't A and C equivalent?

Answer 1

Both A and C are correct.

+----------------------------------------+----------------------------------------------+
|                Truncate                |                    Delete                    |
+----------------------------------------+----------------------------------------------+
| We can't Rollback after performing     | We can Rollback after delete.                |
| Truncate.                              |                                              |
|                                        |                                              |
| Example:                               | Example:                                     |
| BEGIN TRAN                             | BEGIN TRAN                                   |
| TRUNCATE TABLE tranTest                | DELETE FROM tranTest                         |
| SELECT * FROM tranTest                 | SELECT * FROM tranTest                       |
| ROLLBACK                               | ROLLBACK                                     |
| SELECT * FROM tranTest                 | SELECT * FROM tranTest                       |
+----------------------------------------+----------------------------------------------+
| Truncate reset identity of table.      | Delete does not reset identity of table.     |
+----------------------------------------+----------------------------------------------+
| It locks the entire table.             | It locks the table row.                      |
+----------------------------------------+----------------------------------------------+
| Its DDL(Data Definition Language)      | Its DML(Data Manipulation Language)          |
| command.                               | command.                                     |
+----------------------------------------+----------------------------------------------+
| We can't use WHERE clause with it.     | We can use WHERE to filter data to delete.   |
+----------------------------------------+----------------------------------------------+
| Trigger is not fired while truncate.   | Trigger is fired.                            |
+----------------------------------------+----------------------------------------------+
| Syntax :                               | Syntax :                                     |
| 1) TRUNCATE TABLE table_name           | 1) DELETE FROM table_name                    |
|                                        | 2) DELETE FROM table_name WHERE              |
|                                        |    example_column_id IN (1,2,3) 

Ref: https://stackoverflow.com/a/12900557

 

Answer 2

DML (Data Manipulation Language) commands can be rolled back, DDL (Data Definition Language) commands can’t.

TRUNCATE is a DDL command, while DELETE is a DML command.

 

Both Options A and C are correct, but if anything I'd choose Option A because giving COMMIT command after DELETE has no role in retrieving lost data, making Option C slightly technically inaccurate.

Answer 3

TRUNCATE is DDL it involves two commits, one before and one after the statement execution. Truncate can therefore not be rolled back, and a failure in the truncate process will have issued a commit anyway. Delete can be rolled back https://stackoverflow.com/questions/139630/whats-the-difference-between-truncate-and-delete-in-sql

Answer 4

OPTION A. WE CAN NOT ROLLBACK AFTER TRUNCATE BUT ROLLBACK AFTER DELETE.