DELETE vs TRUNCATE behavior in SQL - Practice Questions

Challenge - 5 Problems

❓ query_result

intermediate

Effect of DELETE on transaction logs

Consider a table Employees with 1000 rows. You run the query:

DELETE FROM Employees;

What is the effect on the transaction log?

SQL

DELETE FROM Employees;

A Each row deletion is logged individually, causing a large transaction log.

B Only the page deallocations are logged, making the log small.

C No logging occurs because DELETE is a non-logged operation.

D The entire table is dropped and recreated, so the log is minimal.

💡 Hint

Think about how DELETE handles each row versus TRUNCATE.

DELETE logs each row deletion individually, which can cause a large transaction log especially for many rows.

❓ query_result

intermediate

TRUNCATE and identity column reset

Given a table Orders with an identity column starting at 1, after inserting 10 rows, you run:

TRUNCATE TABLE Orders;

What happens to the identity value for the next inserted row?

SQL

TRUNCATE TABLE Orders;

A The identity value is set to NULL and must be reseeded manually.

B The identity value continues from 11 for the next insert.

C The identity value resets to 1 for the next insert.

D An error occurs because TRUNCATE cannot be used on tables with identity columns.

💡 Hint

TRUNCATE resets the table state including identity counters.

TRUNCATE removes all rows and resets the identity seed to its original starting value.

🧠 Conceptual

advanced

Difference in locking behavior between DELETE and TRUNCATE

Which statement correctly describes the locking behavior when running DELETE vs TRUNCATE on a large table?

A Neither DELETE nor TRUNCATE acquire any locks during execution.

B DELETE acquires a table-level lock; TRUNCATE acquires row-level locks.

C Both DELETE and TRUNCATE acquire only page-level locks.

D DELETE acquires row-level locks; TRUNCATE acquires a table-level lock.

💡 Hint

Think about how each command affects concurrency and locking.

DELETE locks rows individually allowing more concurrency; TRUNCATE locks the entire table to quickly remove all data.

🔧 Debug

advanced

Why does TRUNCATE fail on a table with foreign key constraints?

You try to run:

TRUNCATE TABLE Customers;

but get an error about foreign key constraints. Why does this happen?

A TRUNCATE requires the table to have at least one index to run.

B TRUNCATE cannot be used on tables referenced by foreign keys because it does not check constraints row by row.

C TRUNCATE only works on empty tables, so it fails if rows exist.

D TRUNCATE is not supported on tables with any indexes.

💡 Hint

Think about how TRUNCATE bypasses row-level operations.

TRUNCATE removes all rows without checking each row against foreign key constraints, so it fails if other tables reference it.

❓ optimization

expert

Choosing DELETE vs TRUNCATE for performance and rollback needs

You need to remove all rows from a large table quickly but also want the ability to rollback the operation if needed. Which option is best?

A Use DELETE because it logs each row deletion and supports rollback.

B Use DELETE with NOLOGGING option to speed up deletion and allow rollback.

C Use TRUNCATE because it is faster and supports rollback fully.

D Use TRUNCATE with a transaction savepoint to enable rollback.

💡 Hint

Consider how logging and rollback work for each command.

DELETE logs each row deletion allowing rollback, while TRUNCATE is minimally logged and cannot be rolled back in many systems.