search
Log In
2 votes
716 views

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
in Databases
edited by
716 views
0
Aren't A and C equivalent?

4 Answers

0 votes

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

0 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

 

0 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.

0 votes
OPTION A. WE CAN NOT ROLLBACK AFTER TRUNCATE BUT ROLLBACK AFTER DELETE.
Answer:

Related questions

4 votes
4 answers
1
792 views
The SQL query SELECT columns FROM TableA RIGHT OUTER JOIN TableB ON A.columnName = B.columnName WHERE A.columnName IS NULL returns the following: All rows in Table $B$, which meets equality condition above and, none from Table $A$ which meets the condition. All rows ... condition. All rows in Table $B$, which meets the equality condition All rows in Table $A$, which meets the equality condition
asked Jan 13, 2020 in Databases Satbir 792 views
1 vote
1 answer
2
301 views
Raymonds tree based algorithm ensures no starvation, but deadlock may occur in rare cases no deadlock, but starvation may occur neither deadlock nor starvation can occur deadlock may occur in cases where the process is already starved
asked Jan 13, 2020 in Databases Satbir 301 views
4 votes
13 answers
3
3.3k views
If every non-key attribute functionally dependent on the primary key, then the relation will be in First normal form Second normal form Third normal form Fourth Normal form
asked Jan 13, 2020 in Databases Satbir 3.3k views
4 votes
2 answers
4
798 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 Bits) Under what conditions $Z$ will be $1$? $X>Y$ $X<Y$ $X=Y$ $X!=Y$
asked Jan 13, 2020 in Digital Logic Satbir 798 views
...