[Original] Delete vs truncate table in SQL Server

In SQL Server, each SQL server operation will have a certain log record. In comparison, the insert, update, or delete event will be used. Each operation will lock the row and then record the row operation.

When a table contains many data rows and hundreds of thousands or more, truncate table is faster when it is deleted. It is a fast and non-logging method. The truncate table function is the same as the delete statement without the WHERE clause. However, truncate table is faster and uses fewer system resources and transaction log resources.

Compared with the delete statement, truncate table has the following advantages:

• the transaction log space is small.

  the delete statement deletes a row and records one row in the transaction log. Truncate table deletes data by releasing the data page used to store table data, and only records the page release in transaction logs.

• the number of locks used is usually small.

  when the delete statement is executed using the row lock, the rows in the table are locked for deletion. Truncate table always locks tables and pages, rather than locking rows.

• without exceptions, no pages are left in the table.

  after the delete statement is executed, the table still contains blank pages. For example, you must use at least one exclusive (lck_m_x) Table lock to release empty tables in the heap. If the table lock is not used during the delete operation, the table (HEAP) contains many blank pages. For indexes, the delete operation leaves some blank pages, although these pages are quickly released through the background clearing process.

Similar to the delete statement, the definition of the table cleared using the truncate table is retained in the database together with its index and other associated objects.