In most standard SQL implementations, which of the following statements…
2024
In most standard SQL implementations, which of the following statements regarding DROP and TRUNCATE commands is incorrect?
- A.
DROP removes both the table structure and table data.
- B.
TRUNCATE removes all rows while preserving the table structure.
- C.
TRUNCATE is generally faster than DELETE for removing all rows from a table.
- D.
TRUNCATE can always be reversed using ROLLBACK regardless of the DBMS implementation.
- E.
DROP removes associated indexes and constraints along with the table.
Attempted by 38 students.
Show answer & explanation
Correct answer: D
The question asks which statement is INCORRECT. The false statement is: "TRUNCATE can always be reversed using ROLLBACK regardless of the DBMS implementation."
TRUNCATE is a DDL (Data Definition Language) command. In many systems it is implicitly auto-committed, so it cannot be undone with ROLLBACK. In MySQL (InnoDB) and Oracle, TRUNCATE auto-commits and is NOT rollback-able. Only some systems, such as SQL Server and PostgreSQL, allow TRUNCATE inside an explicit transaction where ROLLBACK can reverse it.
Because the behaviour depends on the DBMS, the claim that TRUNCATE can ALWAYS be rolled back REGARDLESS of the implementation is false.
The other statements are all correct: DROP deletes the table structure, data, and its associated indexes/constraints; TRUNCATE removes all rows while keeping the empty table structure; and TRUNCATE is generally faster than DELETE because it deallocates data pages instead of logging each row deletion.