Unknown facts about Truncate and Delete Statement

Have you ever thought why do we use truncate and delete if both the statements just remove the records?

We will discuss this in our todays topic as how internally they are different from each other. First let’s understand the basics of both the statements.

Truncate :-

  • Truncate does not take where clause as it removes all the records from the table.
  • Truncate does not require commit command to commit the changes.
  • Truncate is fast compared Delete Statement.

Syntax:-

TRUNCATE TABLE <TABLE_NAME>;

Delete :-

  • We can Delete some specific records by using where clause and then it remove only those records.
  • Delete requires commit command to commit the changes.
  • Delete is slow compared to Truncate.

Syntax:-

DELETE FROM <TABLE_NAME> WHERE <EXPRESSION>;

Main difference between Truncate and Delete is the removal of the data from the table. To understand this concept we should understand how data is getting stored in the SQL.

Storing Logic: Data in SQL get stored in the pages.

So, the truncate directly removes all the pages from the SQL but in Delete SQL removes each rows of the table one by one. Because of this difference Delete is slower than the Truncate. One more thing as we have discussed that the truncate removes all the pages so because of that, it does not require commit statement.

Bhagavad Gita Verse:-

The Supreme Divine Personality said: Every human being is born with innate faith, which can be of three kinds- saattvic, raajasic or taamasic. Now hear about this from me.

I am a Software Developer in MNC company and I do Freelancing and blogging as well.