Discussion in 'SQL Server' started by premchandra, Oct 9, 2013.
Which one is faster DELETE/TRUNCATE?
TRUNCATE is faster, as it does not actually "deletes" the data, but instead, it marks the extents as empty and free for use. On the other hand, DELETE is a "per row" operation, which actually empties the extents' contents, thus performing slower.
The delete operation can be rolled back or undo after deleting something from the database. Delete is used to remove records form the data base and it is a DML command. To make the changes done by the delete statement permanent, we use commit and rollback transaction.
DML stands for Data Manipulation Language. The DML statements are used for managing data in database. The changes made by the DML commands are not permanent, they can be rolled back.
Truncate is a DDL command. DDL stands for Data Definition Language. DDL statements are used to define the database structure or schema.
Truncate is used to remove the all the records of a table but it does not remove the data structure of the table. The changes made by the truncate command cannot be undo or rolled back. Truncate command is faster than delete command because it uses fewer system and transaction log resources. The truncate command is similar to delete but it does not use the WHERE clause.
TRUNCATE will be executed faster than a DELETE if you want to quickly delete all rows from a table.
Separate names with a comma.