Trunacate And Delete Operation in SqlServer

Question

What is the difference between the Truncate and Delete ? Which is most suitable?

Aarti · Answer

Hi Michell Truncate: 1. It is a DDL Command 2. by using Truncateall rows of a table deleted TRUNCATE TABLE Table_Name; 3. including all spaces allocated for the records are ALSO removed 4. Its Auto Commited TRUNCATE TABLE Table_Name; Delete: 1. It is a DML Command 2. by using Deletetewe have option to delete all rows of a table or by using where clause we can delete particular records. DELETE FROM Table_Name ; DELETE FROM Table_Name WHERE ColumnID; 3. the space for the records remain. 4. Need to commit by using Commit Command. Thanks.

Satyapriya Nayak · Answer

Hi Michell Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command. TRUNCATE • TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. • TRUNCATE removes the data by deallocating the data pages used to store the table's data and only the page deallocations are recorded in the transaction log. • TRUNCATE removes all rows from a table but the table structure its columns constraints indexes and so on remains. The counter used by an identity for new rows is reset to the seed for the column. • You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged it cannot activate a trigger. • TRUNCATE cannot be rolled back. • TRUNCATE is DDL Command. • TRUNCATE Resets identity of the table DELETE • DELETE removes rows one at a time and records an entry in the transaction log for each deleted row. • If you want to retain the identity counter use DELETE instead. If you want to remove table definition and its data use the DROP TABLE statement. • DELETE Can be used with or without a WHERE clause • DELETE Activates Triggers. • DELETE can be rolled back. • DELETE is DML Command. • DELETE does not reset identity of the table. Thanks

Javeed M Shaikh · Answer

Hi Michell Following are the differences: TRUNCATE is faster than delete cause it does not actually removes the rows but it deallocates the data pages and pointers. These pages are later overwritten or freed when database is shrunk. TRUNCATE information is also logged by making entries of data pages in the log file. You can use TRUNCATE in transactions also. DELETE on the other hand logs data for each row getting removed so it is slow and takes more resource to perform the operation. DELETE can also be used in transactions. IF you have a lot of rows to delete than use TRUNCATE otherwise DELETE but if you are planning to do some recovery where individual logging of rows is important use DELETE. Please do not forget to mark Accepted Answer.