SQL DELETE
Q1. What is the SQL DELETE command?
DELETE is a Data Manipulation Language command used to remove rows from a table based on a condition. It is transactional and supports rollback. DELETE operations generate logs for each row, which can impact performance on large tables.
Q2. DELETE vs TRUNCATE – what is the difference?
DELETE removes selected rows and supports rollback and triggers. TRUNCATE removes all rows instantly, is faster, but cannot be rolled back in most databases.
Q3. How do you safely delete large volumes of data?
Large deletes should be performed in batches using primary key ranges or limits. This approach reduces lock contention and log growth.
Q4. What is a soft delete and why is it used?
A soft delete marks records as inactive instead of physically removing them. This approach preserves historical data and supports auditing and recovery.
Q5. How does DELETE affect indexes and performance?
DELETE operations update indexes and can cause fragmentation. Regular maintenance such as index rebuilding helps maintain performance.