When it comes to deleting data from a table, there are two main options: TRUNCATE and DELETE. TRUNCATE is faster than DELETE, since it doesn't scan all records before deleting them. It locks the entire table to delete data from a table, and therefore uses less transaction space than DELETE. Unlike DELETE, TRUNCATE does not return the number of rows deleted from the table.
Truncating is faster than deleting because it only resets the high watermark. Delete performs operations on each record (delete, reverse segments). The downside is that once a table is truncated, all the space it used before is immediately freed up. Once the truncation is executed, the space occupied by the previous table is free and can be used by any other session.
Therefore, no confirmation is needed, it is not possible to revert, and you can only delete all data, not a subset. Basically, truncate removes the entire data portion of the table. While deleting removes items from the data one by one. Truncate is faster than the delete statement because the delete statement generates redo while truncating generates a minimum of redo.
Because delete will have a copy of the deleted element where truncate will not have a copy, then delete is used to delete from the particular form and truncate will remove it from the entire structure. The TRUNCATE TABLE statement deletes all rows from a table faster than DELETE. Logically, TRUNCATE TABLE is similar to the DELETE statement without a WHERE clause. It's faster than the delete command and does not require a commit for the changes to be permanent.
Truncate Table also deletes all rows from a table, but it will not record the deletion of each row, but will record the deallocation of the data pages in the table, making it faster. When you issue a TRUNCATE TABLE statement, you instruct SQL Server to delete all records in a table, without any log or transaction processing taking place. In short, truncating does not record anything (so it's much faster but can't be undone), while deletion is recorded (and may be part of a larger transaction, will be rolled back, etc.). At a high level, you might consider the truncate command to be similar to a Delete command without a Where clause. Deletion can be granted in a table to another user or role, but it cannot be truncated without using a DROP ANY TABLE lease. The DELETE statement can have a WHERE clause to delete specific records, while the TRUNCATE statement does not require any and deletes the entire table.
Because TRUNCATE TABLE is a DDL (data definition language) command, not a DML (data manipulation language) command, delete triggers are not executed. However, I have seen unintentionally truncating referential integrity and violating other constraints. In conclusion, when you want to delete all records from any table quickly and efficiently, you should use truncate instead of delete as it will save time and resources. Truncating does not record anything (so it's much faster but can't be undone), while deletion is recorded (and may be part of a larger transaction, will be rolled back, etc.). Deletion can be granted in a table to another user or role, but it cannot be truncated without using a DROP ANY TABLE lease.
