Other SQL Commands

Other SQL commands beyond everyday CRUD include ALTER, DROP and TRUNCATE. ALTER TABLE changes an existing table's structure without removing its data, DROP TABLE removes the table entirely along with all its data, and TRUNCATE TABLE quickly removes every row while keeping the table's structure, typically much faster than DELETE with no condition.

3 min read

Beyond the everyday CREATE, SELECT, INSERT, UPDATE, and DELETE, a few other SQL commands come up regularly for changing table structure or clearing out data in bulk. Each one has a distinct effect that's worth knowing precisely, since confusing them can cause real, sometimes irreversible, damage.

ALTER: changing an existing table's structure

ALTER TABLE modifies a table that already exists: adding a column, removing a column, changing a column's data type, or renaming something.

ALTER TABLE students ADD COLUMN email VARCHAR(150);
ALTER TABLE students DROP COLUMN grade;
ALTER TABLE students RENAME COLUMN name TO full_name;

DROP: removing an entire object

DROP TABLE removes a table completely, its structure, all its data, and any indexes or constraints attached to it. There's no "empty the table but keep it" option here, DROP means the object stops existing.

DROP TABLE old_logs;

TRUNCATE: emptying a table fast, but keeping its structure

TRUNCATE TABLE removes every row from a table, but keeps the table's structure intact, ready to be filled again. It's typically much faster than DELETE FROM table_name with no condition, because it doesn't log each row deletion individually the way DELETE does, it deallocates the data pages directly.

TRUNCATE TABLE session_logs;

The real distinction that matters

DELETE (with no WHERE clause) removes all rows one at a time, is slower on large tables, and can be rolled back within a transaction in most databases. TRUNCATE removes all rows at once by deallocating storage directly, is much faster, but in many databases can't be rolled back once committed, or resets auto-increment counters as a side effect. DROP removes the table itself, structure and all, there's nothing left to insert into afterward without recreating it.

Where this matters in real systems

A common real production pattern is using TRUNCATE to quickly clear a staging table between nightly batch-processing runs (fast, and the table structure needs to persist for the next run), while DROP is reserved for genuinely retiring a table that's no longer needed at all. Mixing these up, running DROP when TRUNCATE was meant, is a real, recoverable-only-from-backup mistake that has happened to real production systems more than once.

What interviewers actually ask about ALTER, DROP, and TRUNCATE

  • "What's the difference between DELETE, TRUNCATE, and DROP?" DELETE removes matching rows one at a time and keeps the table; TRUNCATE removes all rows at once (faster) and keeps the table; DROP removes the table entirely, structure and all.
  • "Why is TRUNCATE usually faster than DELETE with no condition?" TRUNCATE deallocates the underlying data pages directly instead of logging and removing each row individually.
  • "Can TRUNCATE be rolled back?" It depends on the database, but in many systems it either can't be rolled back at all or behaves differently from a normal transactional DELETE, so it should be treated as effectively permanent.
  • "When would you use ALTER TABLE?" Whenever an existing table's structure needs to change: adding, removing, or modifying a column, without losing the data already in the table.

Key takeaways

  • ALTER TABLE changes an existing table's structure without removing its data.
  • DROP TABLE removes the table entirely, structure and data both, permanently.
  • TRUNCATE TABLE removes all rows quickly while keeping the table's structure intact.
  • DELETE, TRUNCATE, and DROP look similar but have meaningfully different scopes, speeds, and reversibility, mixing them up is a real, damaging mistake.
All 174 DBMS interview questions

Log in to save which topics you've read across every subject.

Frequently asked questions

What's the difference between DELETE, TRUNCATE, and DROP?

DELETE removes matching rows one at a time and keeps the table; TRUNCATE removes all rows at once (faster) and keeps the table; DROP removes the table entirely, structure and all.

Why is TRUNCATE usually faster than DELETE with no condition?

TRUNCATE deallocates the underlying data pages directly instead of logging and removing each row individually.

Can TRUNCATE be rolled back?

It depends on the database, but in many systems it either can't be rolled back at all or behaves differently from a normal transactional DELETE, so it should be treated as effectively permanent.

When would you use ALTER TABLE?

Whenever an existing table's structure needs to change: adding, removing, or modifying a column, without losing the data already in the table.