Tuesday, 20 October 2015

Interview Question - Write the SQL script to delete/Truncate data from all the tables of the specific database

Recently, I came across this question where candidate had to write a query to empty all the tables from the specific database. Prima facie it feels like a cakewalk for the candidate but believe me it's not. Why not ? Because one must consider the relationships between the tables while writing the query for this question. It could be multilevel hierarchy that one needs to identify before even thinking about deleting the data from the child tables.

So the question gets divided into 2 parts 

  • Identify the relationship hierarchy
  • Start deleting the data from the bottom i.e from child tables to the parent tables
Now the real question, is it really necessary to identify the hierarchy between tables ? Isn't there any other way to perform this ?