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.

Practice

1. Which statement correctly describes the difference between DELETE and TRUNCATE in SQL?

easy

A. TRUNCATE is slower than DELETE because it logs each row removal.

B. TRUNCATE removes rows one by one and can use a WHERE clause; DELETE removes all rows quickly without logging each row.

C. DELETE and TRUNCATE both remove rows one by one and support WHERE clauses.

D. DELETE removes rows one by one and can use a WHERE clause; TRUNCATE removes all rows quickly without logging each row.

Solution

1. Step 1: Understand DELETE behavior

   DELETE removes rows individually and supports filtering with WHERE.

2. Step 2: Understand TRUNCATE behavior

   TRUNCATE removes all rows quickly without logging each row, and does not support WHERE.

3. Final Answer:

   DELETE removes rows one by one and can use a WHERE clause; TRUNCATE removes all rows quickly without logging each row. -> Option D

4. Quick Check:

   DELETE = selective row removal, TRUNCATE = fast full clear [OK]

Hint: DELETE filters rows; TRUNCATE clears all fast [OK]

Common Mistakes:

• Thinking TRUNCATE supports WHERE clause
• Confusing which command logs each row
• Believing DELETE is faster than TRUNCATE

2. Which of the following is the correct syntax to remove all rows from a table named Employees using TRUNCATE?

easy

A. DELETE FROM Employees;

B. TRUNCATE TABLE Employees;

C. TRUNCATE Employees WHERE id > 10;

D. REMOVE ALL FROM Employees;

Solution

1. Step 1: Recall TRUNCATE syntax

   The correct syntax to remove all rows quickly is TRUNCATE TABLE table_name;.

2. Step 2: Check options for correctness

   DELETE FROM Employees; uses DELETE; REMOVE ALL FROM Employees; is invalid SQL; TRUNCATE Employees WHERE id > 10; incorrectly adds WHERE to TRUNCATE; TRUNCATE TABLE Employees; matches the correct syntax.

3. Final Answer:

   TRUNCATE TABLE Employees; -> Option B

4. Quick Check:

   TRUNCATE syntax = TRUNCATE TABLE [OK]

Hint: TRUNCATE always uses TABLE keyword [OK]

Common Mistakes:

• Adding WHERE clause to TRUNCATE
• Using DELETE syntax for TRUNCATE
• Using non-existent REMOVE command

3. Given a table Orders with 1000 rows, what will be the number of rows after executing DELETE FROM Orders WHERE OrderDate < '2023-01-01'; if 300 rows have OrderDate before 2023?

medium

A. 700 rows remain

B. 0 rows remain

C. 1000 rows remain

D. Syntax error

Solution

1. Step 1: Understand DELETE with WHERE clause

   The DELETE command removes only rows matching the condition: OrderDate < '2023-01-01'.

2. Step 2: Calculate remaining rows

   Since 300 rows match the condition, they are deleted. Remaining rows = 1000 - 300 = 700.

3. Final Answer:

   700 rows remain -> Option A

4. Quick Check:

   1000 - 300 = 700 rows left [OK]

Hint: DELETE with WHERE removes only matching rows [OK]

Common Mistakes:

• Assuming DELETE removes all rows without WHERE
• Confusing DELETE with TRUNCATE behavior
• Thinking syntax error due to date format

4. You run the command TRUNCATE TABLE Customers WHERE Country = 'USA'; but get an error. What is the most likely cause?

medium

A. TRUNCATE does not support WHERE clauses.

B. The table Customers does not exist.

C. TRUNCATE requires a WHERE clause.

D. You must use DELETE instead of TRUNCATE for all tables.

Solution

1. Step 1: Check TRUNCATE command rules

   TRUNCATE removes all rows and does not allow filtering with WHERE.

2. Step 2: Identify error cause

   Using WHERE with TRUNCATE causes a syntax error because TRUNCATE does not support WHERE clauses.

3. Final Answer:

   TRUNCATE does not support WHERE clauses. -> Option A

4. Quick Check:

   TRUNCATE = no WHERE allowed [OK]

Hint: TRUNCATE never uses WHERE clause [OK]

Common Mistakes:

• Assuming TRUNCATE supports WHERE
• Thinking table absence causes this error
• Believing DELETE must always replace TRUNCATE

5. You want to quickly remove all rows from a large table Logs but keep the table structure and reset identity counters. Which command should you use and why?

hard

A. Use DELETE FROM Logs WHERE 1=1; because it is faster than TRUNCATE.

B. Use DELETE FROM Logs; because it removes rows and resets identity counters.

C. Use TRUNCATE TABLE Logs; because it removes all rows fast and resets identity counters.

D. Use DROP TABLE Logs; to remove all rows and keep structure.

Solution

1. Step 1: Identify command for fast full table clearing

   TRUNCATE TABLE removes all rows quickly and resets identity counters.

2. Step 2: Compare with DELETE and DROP

   DELETE removes rows slowly and does not reset identity counters; DROP removes table structure.

3. Final Answer:

   Use TRUNCATE TABLE Logs; because it removes all rows fast and resets identity counters. -> Option C

4. Quick Check:

   TRUNCATE = fast clear + reset identity [OK]

Hint: TRUNCATE clears fast and resets identity counters [OK]

Common Mistakes:

• Thinking DELETE resets identity counters
• Using DROP when structure must be kept
• Believing DELETE is faster than TRUNCATE