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.
Table of Contents
Delete -Truncate Basic differences are:
| DELETE | Truncate |
|---|---|
| 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.
- 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.
- 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.
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:
- SQL server Column Encryption using symmetric key.
- How to Update using Select statement in SQL Server.
- SQL Server Interview Questions and Answers.
- Import One Database to another in SQL server.
- Generate database scripts in SQL server.
- Different ways to search SQL objects in database.
- SQL CASE statement
- SQL IIF function
- QUOTENAME function in SQL
- How to Take SQL Server Database Offline/Online
- How to use SQL ORDER BY in Your Queries
- SQL Paging using OFFSET and FETCH
- How is data physically stored in a SQL database
- Fill Factor in SQL Server
- How to Search SQL Object in Complete Database
- How to generate SQL table script with data
- Delete Truncate and disk storage.