TRUNCATE against DELETE — Aptitude Question
TRUNCATE against DELETE: Which command removes every row of a table while keeping the table itself, and is classed… Answer and worked solution on CodeKairo.
- Topic: OS, DBMS & Networks
- Section: Programming MCQs
- Difficulty: Easy
- Time target: 60 seconds
Question
Which command removes every row of a table while keeping the table itself, and is classed as DDL rather than DML?
Options
- DELETE
- DROP
- TRUNCATE
- UPDATE
Show the answer and the worked solution
Correct answer: C. TRUNCATE
Worked solution
- DELETE is DML: it removes rows one at a time, logs each of them and may carry a WHERE clause.
- DROP removes the table definition itself, not merely its contents.
- TRUNCATE deallocates the data pages in a single DDL operation, leaving an empty table with its columns, indexes and constraints intact.
Answer: TRUNCATE.
The idea
TRUNCATE is fast because it does not log individual rows, which is also why it cannot be filtered with WHERE and, in most engines, resets identity counters.
Shortcut. DROP removes the table itself and UPDATE removes nothing; of the two that empty a table, DELETE is DML, so TRUNCATE.
Common trap. "DELETE" does remove every row when given no WHERE and keeps the table, but it is DML, not DDL.