What is the difference between truncate and delete which is faster

Your email address will not be published. Save my name, email, and website in this browser for the next time I comment. Skip to content Search for: search Search. Previous Previous post: Why can it take so long to drop a clustered index?

Delete works on individual rows and delete the data. Therefore, it activates a trigger. The truncate command cannot activate a trigger. The trigger is activated if any row modification takes place. In this command, SQL Server deallocates all pages, so it does not activate a trigger. Delete command removes the rows matched with the where clause. It also does not remove the columns, indexes, constraints, schema. The truncate command only removes all rows of a table.

It does not remove the columns, indexes, constraints, and schema. I hope this article helps to answer all your questions related to delete and truncate commands in SQL Server and also understand the difference in these commands.

You need to be careful while using the truncate command because it removes all rows in a table. Rollback transaction. Author Recent Posts. Rajendra Gupta. As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. 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. Searching and finding a string value in all columns in a SQL Server table. View all my tips. Back To Top Hi All, according to a post in the same website i.

It can be executed for any row change or when all rows are removed. Be careful using this command. It also resets the table auto-increment value to the starting value usually 1. But it is not used for simply removing data from a table; it deletes the table structure from the database, along with any data stored in the table.

This removes all data in the table product and the structure of the table.