SQL Server Interview Question #14

What is the difference between DELETE, TRUNCATE, and DROP?

SQL Server Fundamentals Junior Beginner

Detailed Explanation

DELETE removes rows and can use a WHERE condition to remove selected records. TRUNCATE removes all rows from a table, does not support WHERE, and normally resets an identity counter to its seed value. DROP removes the database object itself, including its table definition.

A common interview misconception is that TRUNCATE can never be rolled back. In SQL Server, TRUNCATE TABLE can be rolled back when it is executed inside an explicit transaction that has not yet been committed.

Code Example

BEGIN TRANSACTION;

TRUNCATE TABLE Products;

ROLLBACK TRANSACTION;