A table has 100 million records. You want to remove all rows as quickly as possible while keeping the table. Which SQL command will you use?
Interview preparation resource from Gate Smashers.
Use TRUNCATE TABLE to remove every row quickly while keeping the table definition.
TRUNCATE TABLE Employees;Direct answer
Use TRUNCATE TABLE when the requirement is to remove every row quickly while retaining the table definition.
Why TRUNCATE is faster
TRUNCATE normally deallocates data pages rather than deleting rows one by one, so it usually generates less logging and is faster than an unrestricted DELETE.
- Mechanism: Deallocates data pages instead of row-by-row deletes.
- Logging: Typically generates less transaction log activity, improving speed.
Caveats and when to use DELETE instead
The exact transaction, trigger, identity-reset and foreign-key behaviour of TRUNCATE varies by database platform. Use DELETE when you need behaviour that TRUNCATE may not provide.
- Filtered deletes: Use DELETE when you need to remove only a subset of rows (filtering).
- Triggers: Use DELETE when DELETE triggers must run.
- Platform restrictions: Use DELETE when the platform's TRUNCATE restrictions are unacceptable or its behaviour differs from your requirements.
Example
See the example SQL code block below for the basic TRUNCATE usage.
BasisTRUNCATE TABLEDELETE
OperationDeallocates data pages (not row-by-row).Deletes rows one by one.
LoggingUsually generates less logging.Generally generates more logging.
Typical use caseRemove all rows quickly while keeping the table definition.Remove specific rows, or when triggers must run or TRUNCATE is restricted.
Triggers / DB-specific behaviourBehaviour varies by database (may differ for triggers, identity reset, FK handling).DELETE triggers must run; behaviour is the standard row-delete path.