SQL Interview Questions and Answers on deleting records

The following is a very popular SQL job interview question.

Q. What is the difference between "Truncate" and "Delete" commands?
A. 

• TRUNCATE TABLE_NAME always locks the table and page but not each row, whereas  DELETE statement is executed using a row lock, each row in the table is locked for deletion.
• Truncate removes all the records in the table whereas delete can be used with WHERE clause to remove records conditionally. That is remove only a handful number of records.
• Truncate performance is much faster than Delete, as its logging is minimal wheres the Delete command logs every record.
• Truncate does not retain the identity, whereas DELETE command retains the identity. When you use Truncate, If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column.
• Truncate cleans up the object statistics and clears the allocated space whereas Delete retains the object statistics and allocated space.
• TRUNCATE is a DDL (Data Definition Language) and DELETE is a DML (Data Manipulation Language). 
• Data removed by TRUNCATE command cannot be generally rolled back unless the database server specifically supports it. The DELETE command can rollback a transaction.
• The TRUNCATE command does not fire any triggers, whereas the DELETE command fires any triggers defined on the table. For example, to keep an audit trail of records that have been deleted by inserting the deleted records into an audit table via the DELETE triggers.

Q. When will you use a truncate command?
A.TRUNCATE is useful for purging a table with huge amount of data. Alternatively, you can drop the table and recreate it that makes sense. Firing a delete command instead of a truncate command to empty a table with millions of records can result in locking the whole table and also can take longer time to complete, and at times cause the machine to hang.

The truncate command is executed as shown below.

TRUNCATE TABLE table_name

Q. Which command will you use to periodically purge data from your tables as part of a house keeping job?
A.  Use a DELETE command within a transaction with a WHERE clause to remove data that are older than 7 years. Remove  large amount of data in batches as opposed to in a single transaction.

Q. How will you delete a few records from single table
A.

DELETE FROM parent p WHERE p.parent_name = 'Peter'

Q. How will you delete a few records from parent and child tables where the parent table with parent_name = 'Peter'?
A.

Firstly, you need to delete the child records because the integrity constraint won't let you delete the parent record when there are child records.

DELETE child

FROM  parent p, child c

WHERE p.parent_id = c.parent_id

  AND p.parent_name = 'Peter'

Now, the parent table can be deleted as shown below

DELETE FROM  parent p WHERE p.parent_name = 'Peter'

Note: Please note the difference in syntax when you make a join with the child. When there is only a single table involved, it is "DELETE FROM table_name", but when there is a join, it is "DELETE table_name" and then the "FROM" with the join clauses.


Q.  What do you do with the PURGE command?
A. The purge command is used to clear the recycle bin. It is generally used with the DROP command. For example,

drop table tablename purge;

the above  command will clear away the table from database as well as from the recycle bin. After executing  the purge command, you cannot retrieve the table using a flashback query. 

You may also like:

• Database interview Questions and answers
• SQL Interview Questions and Answers
•  SQL interview questions and answers - scenarios based
•  SQL subqueries interview questions and answers
•  SQL Interview Questions and Answers: storing a tree structure in a database table
•  Excel spreadsheet to generate SQL
•  SQL Tutorial with HSQLDB