Delete Truncate and disk storage

In this article we’ll discuss the difference between delete and truncate in term of storage disk space. Along with its basic differences we’ll also discuss how these differ in term of storage space. Basically, DELETE operation do not release the disk space occupied by the table whereas truncate release the disk space occupied by the data present in the table.

Delete -Truncate Basic differences are:

It is DDL command. It is DML Command.
Identity reseeds are not done.Identity reseed on truncate command.
After Delete Roll back feasible.After Truncate Roll back is NOT feasible.
DISK space not released fully.DISK space released.

Let’s check out the difference in term of ‘Disk Storage’:

  • Take a table which has some data in it, or you can create new table and insert some dummy data. I have “NorthWND” DB and I have created one “test” table in it with data. Right click on this table and choose properties.
1 Truncate DEL -
  • Please note “Data Space” is 0.234 MB which is the space used by data stored in table. Now I’ll run DELETE query on this table and will delete all the rows from this table.
  • After Deleting all records there is no record in “Test” table as shown below. “Data Space” is there and “Index space” is still not zero even I have no data in this table.  
Delete Truncate and disk storage
  • Now let’s perform TRUNCATE on this table. Notice in below given screen shot “Index Space” is ZERO now “DISK Space” is also less as compared to the DELETE case. In current case it is 0.016 MB whereas in DELETE case it was 0.023 MB.    
Delete Truncate and disk storage

From above query we can say that TRUNCATE reset the index and clear more memory as compared to DELETE query.

I hope you enjoyed reading this article. In this article you learned about “Delete truncate and disk storage” differences. You can learn more SQL concepts on Microsoft Learn.

For more knowledge of MS SQL, you can check our section HERE

You may also like:

Posted in SQL

Leave a Reply

Your email address will not be published. Required fields are marked *