DELETE vs TRUNCATE behavior in SQL - Quick Revision & Key Differences

Recall & Review

beginner

What is the main difference between DELETE and TRUNCATE in SQL?

DELETE removes rows one by one and logs each deletion, allowing WHERE conditions. TRUNCATE removes all rows quickly without logging individual row deletions and cannot use WHERE.

beginner

Can you use a WHERE clause with TRUNCATE?

No, TRUNCATE removes all rows from a table and does not support a WHERE clause.

intermediate

Which command, DELETE or TRUNCATE, can be rolled back in a transaction?

Both DELETE and TRUNCATE can be rolled back if used inside a transaction, but TRUNCATE behavior depends on the database system.

intermediate

Does TRUNCATE reset identity (auto-increment) counters in a table?

Yes, TRUNCATE usually resets identity counters to the seed value, while DELETE does not reset them.

beginner

Which command is generally faster for removing all rows: DELETE or TRUNCATE?

TRUNCATE is generally faster because it deallocates data pages instead of deleting rows one by one.

Which SQL command allows you to delete specific rows using a condition?

A ALTER

B TRUNCATE

C DELETE

D DROP

DELETE supports WHERE clauses to remove specific rows; TRUNCATE removes all rows without conditions.

What happens to the identity counter when you use TRUNCATE on a table?

A It increments by one

B It is deleted

C It remains unchanged

D It resets to the initial seed value

TRUNCATE resets identity counters to their seed value, unlike DELETE.

Which command is faster for removing all rows from a table?

A DELETE

B TRUNCATE

C UPDATE

D INSERT

TRUNCATE is faster because it deallocates data pages instead of deleting rows individually.

Can TRUNCATE be rolled back in a transaction in all database systems?

A Depends on the database system

B No, never

C Yes, always

D Only if used with WHERE clause

Rollback support for TRUNCATE depends on the database system; some allow it inside transactions, others do not.

Which command logs each row deletion individually?

A DELETE

B TRUNCATE

C DROP

D CREATE

DELETE logs each row deletion, which can slow down performance compared to TRUNCATE.

Explain the differences in behavior between DELETE and TRUNCATE commands in SQL.

Think about speed, logging, and conditions.

When would you choose DELETE over TRUNCATE in managing a database table?

Consider situations needing selective deletion or trigger activation.