← Bank of America Interview Insights

Bank of America·Software Engineer·Technical Phone Screen·Junior

JuniorPrefer not to say
Jun 2026

Summary

SQL interview question that turned into an infinite gotcha loop. Every answer I gave, the interviewer added a new constraint until I had no idea what the right answer even was anymore.

Questions Asked (1)

Q1

What is the difference between DELETE and TRUNCATE, and which would you use to remove all rows from a table?

Technical Trade-offsData Modeling
Author's notes

I knew this cold.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by defining DELETE and TRUNCATE in terms of their SQL semantics, focusing on logging, transaction behavior, and performance. Then compare their use cases, especially in a banking context where data integrity and auditability are critical. Finally, recommend TRUNCATE for removing all rows when referential integrity and transactional logging are not required, but note that DELETE is safer in production environments with foreign keys or when you need to log the operation.

Pro tip: In a bank, always consider the audit trail and recovery implications: TRUNCATE is minimally logged and cannot be rolled back in some databases, so it might violate compliance requirements. Mention that you'd use TRUNCATE only after confirming with DBAs and ensuring no foreign key references, or use DELETE with a WHERE clause if you need to preserve the ability to rollback.

1. Define DELETE

Explain that DELETE is a DML command that removes rows one by one, logs each deletion, can be rolled back, and can include a WHERE clause. It fires triggers and maintains referential integrity.

2. Define TRUNCATE

Explain that TRUNCATE is a DDL command that quickly removes all rows by deallocating data pages, minimally logs the operation, cannot be rolled back in some databases, and does not fire triggers. It resets identity columns and cannot be used with a WHERE clause.

3. Compare key differences

Highlight differences in performance (TRUNCATE is faster), logging (DELETE logs each row, TRUNCATE logs page deallocations), transaction safety (DELETE can be rolled back, TRUNCATE may not be), and impact on triggers and foreign keys.

4. Recommend based on context

For removing all rows, TRUNCATE is generally preferred for speed and resource efficiency, but in a banking environment, consider audit requirements, foreign key constraints, and the need for rollback. If any of these are present, use DELETE or a staged approach.

5. Summarize with a decision rule

Conclude with a clear rule: Use TRUNCATE when you need to quickly remove all rows and don't require transactional logging or rollback; use DELETE when you need conditional deletion, rollback capability, or to maintain referential integrity and audit trails.

Key Points to Mention

  • DELETE is DML, TRUNCATE is DDL
  • DELETE can have a WHERE clause, TRUNCATE cannot
  • DELETE logs individual row deletions, TRUNCATE logs page deallocations (minimal logging)
  • DELETE can be rolled back (if in a transaction), TRUNCATE may not be rollback-able depending on the database
  • DELETE fires triggers, TRUNCATE does not
  • TRUNCATE resets identity columns, DELETE does not
  • TRUNCATE cannot be used on tables referenced by foreign keys, DELETE can

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.