edited by
2,982 views
3 votes
3 votes

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

  1. 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
  2. After the execution of $\text{‘DELETE’}$ and $\text{‘TRUNCATE’}$ operation retrieval is easily possible for the lost data
  3. 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
  4. After the execution of $\text{‘DELETE’}$ and $\text{‘TRUNCATE’}$ operation no retrieval is possible for the lost data
edited by

4 Answers

3 votes
3 votes

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

 

1 votes
1 votes

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:

Related questions

1 votes
1 votes
1 answer
2
Satbir asked Jan 13, 2020
1,790 views
Raymonds tree based algorithm ensuresno starvation, but deadlock may occur in rare casesno deadlock, but starvation may occurneither deadlock nor starvation can occurdead...
5 votes
5 votes
12 answers
3
Satbir asked Jan 13, 2020
8,917 views
If every non-key attribute functionally dependent on the primary key, then the relation will be inFirst normal formSecond normal formThird normal formFourth Normal form
6 votes
6 votes
3 answers
4
Satbir asked Jan 13, 2020
3,849 views
The following circuit compares two $2$-bit binary numbers, $X$ and $Y$ represented by $X_1X_0$ and $Y_1Y_0$ respectively. ($X_0$ and $Y_0$ represent Least Significant Bit...