Quickly delete records and clear tables in the SQL Server database

To delete all rows in a table, the truncate TABLE statement is a fast and non-logged 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 at a time 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.

 

Note
The truncate table function is the same as the delete statement without the WHERE clause: both delete all rows in the table. However, truncate table is faster than delete and uses less system and transaction log resources.

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

Truncate table deletes all rows in the table, but the table structure, its columns, constraints, and indexes remain unchanged. To retain the ID Count value, use Delete instead. To delete table definitions and data, use the drop TABLE statement.

For tables referenced by the foreign key constraint, the truncate table cannot be used, but the delete statement without the WHERE clause should be used. Because the truncate table is not recorded in the log, it cannot activate the trigger.

The truncate table cannot be used in the index view.

-----------------------
Note: The truncate table deletes all rows in the table, and the count value used by the new row ID is reset to the seed of the column.