Difference Between Truncate And Delete

Question

Hi friends I want to know What is the difference between Truncate and Delete in SQL Server?

Chintan Rathod · Answer

Hi Ajay 1>TRUNCATE is a DDL command whereas DELETE is a DML command. 2>TRUNCATE is much faster than DELETE. Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATEit removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data. 3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently. 4>In case of TRUNCATE Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired. 5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause Thanks & Regards ---------------- Chintan Rathod

Kunal Vaishya · Answer

Dear Delete Table means remove record from tabale and truncate means it set like a new table like you have a identity column Companyid now Companyid is 27 you delete record after delete when you insert the record it will take Companyid = 28 in delete case if you truncate the table it will recreate the table when you insert the record you will get Companyid = 1

Satyapriya Nayak · Answer

Hi Ajay 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 and its columns constraints indexes and so on remain. 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 can not 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

selvi subramanian · Answer

Hi Ajay . Delete commanand removes the rows from a table on the basis of the condition that we provide with a where clause. delete removes rows one at a time and records an entry in the transaction log for each deleted row. delete is DML command 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 is faster and uses fewer system and transaction log resources than DELETE. Truncate is DDL Command

Shakuntala Gaur · Answer

Hi Ajay Delete statement removes rows of a table one by one & delete triggers on that table fires. But Truncate removes all rows by deallocating the data pages assigned to the table & only these deallocation are recorded in the transaction log. Cheers