What is the difference between delete and truncate

We might have a question-. We have the following data in the Employee table. EmpID column is an identity column. Once we remove the record, view the record from the table.

In the following screenshot, we can see that identity value is not available because we removed that record from the table. SQL Delete statement does not reset the identity values in a table. We can roll back a transaction using the delete statement. We can use to roll back the transaction. Execute this rollback command and view the records in the table. We can see the deleted record gets a rollback and we can see it in the Employee table.

It removes all rows in a table. SQL Server stores data of a table in the pages. The truncate command deletes rows by deallocating the pages. It makes an entry for the de-allocation of pages in the transaction log. It does not log each row deletion in the transaction log. It clarifies that we have a backup of our database before executing this command because we cannot recover the deleted records using this query.

Therefore, the database backups allow us to restore the data whenever we need it in the future. The truncate statement is a DDL or data definition language command used to removes complete data from the table without removing the table structure.

After executing this command, we cannot rollback the deleted data because the log is not maintained while performing this operation. The truncate command deallocates the pages instead of rows and makes an entry for the deallocating pages instead of rows in transaction logs.

This command locks the pages instead of rows; thus, it requires fewer locks and resources. Note that we cannot use the truncate statement when a table is referenced by a foreign key or participates in an indexed view. In this article, we have made a comparison between delete and truncate statement.

JavaTpoint offers too many high quality services. Mail us on [email protected] , to get more information about given services. Please mail your requirement at [email protected] Duration: 1 week to 2 week. MySQL Tutorial. Next Topic Database vs Schema. Can be used with indexed views. Run the following query to grant access to testuser1 on the tblStudent table: grant ALTER on tblstudent to testuser1 Re-run the truncate table statement: use StudentDB go truncate table tblstudent View the data from the table: The data has been removed from the table.

Visited times, 6 visits today. Subscribe to our digest to get SQL Server industry insides! He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. Write for us CodingSight is open for new authors and partnership proposals. Facebook Twitter LinkedIn. I think there are probably a lot of people in a similar dilemma.

Thanks Atul,for such valuable and understandable article. This blog has really cleared my confusion!!! Related Articles. Delete duplicate rows with no primary key on a SQL Server table. Rolling up multiple rows into a single row and column for SQL Server data. Popular Articles. How to tell what SQL Server versions you are running.

Resolving could not open a connection to SQL Server errors. Ways to compare and find differences for SQL Server tables and data.