Delete, Truncate, Drop

SQL Fundamentals

Hello readers. I am going to explain the SQL concepts in story form. Let us understand the SQL concepts in a funny way.

Imagine our body as a database and consider cells and microbes as tables contained in that database. The cell table has both good cells and bad cells such as cancer cells. Similarly, the microbes table has friendly microbes as well as killer microbes such as viruses.

Three nanobots named DELETE, TRUNCATE and DROP got into a man’s body with a mission of killing the deadliest HIV viruses and cancer cells. The DELETE nanobot is programmed to find and kill certain specific set of viruses and cancer cells. If we are careless and fail to predefine who are all need to be killed, then he would kill the entire friendly microbes along with the fatal viruses. Therefore, to spot and kill the targeted viruses, the DELETE has to be accompanied by WHERE condition.

Our next nanobot TRUNCATE also works alike DELETE nanobot with a slight difference in operation. Instead of deleting the targeted items, he will remove all the contents of a table except the skeleton (fields or structure) of the table. Therefore, all the table contents no matter how good or bad, all stuffs will get deleted. In addition, TRUNCATE eliminates empty spaces in a table. After using TRUNCATE command, we cannot ROLL BACK the deleted items. i.e. dead records cannot be resurrected.

Here it comes the last nanobot DROP who is a devourer of tables and databases. Once the DROP nanobot is activated, he will destroy the entire table or a database based on what purpose he is deployed for.

Thank you for reading this story. Hope you enjoyed it while catching the concept.

Further Reading

Variance between Delete, Drop and Truncate

            These are known as killers of objects and records of a table. Each one of them has its own uniqueness in removing data. For example, the DELETE command which comes under the classification of DML commands deletes rows of a table. If we are careless, this command would delete the entire records. So this query should be accompanied with WHERE clause to specify which record needs to be removed from a table.

Here is the syntax for delete query:

DELETE from Tablename

WHERE condition

            On the other hand, the TRUNCATE command which comes under the category of DDL commands delete all the rows as well as the free space that occupies the table.

Here comes the syntax for Drop command:

                                    TRUNCATE Table Tablename

            Next, the heavy weight boxer named DROP query whose single punch knocks out the entire table (object) from a database. This guy creates all sorts of chaos in the database if we use him unknowingly. The syntax for him is:

DROP Table Tablename