What is Truncate and How Does it Work?

Truncating is a powerful SQL statement that can quickly delete all records from a table. It is similar to the DELETE statement without a WHERE clause, but it is faster and uses fewer system log and transaction resources. Truncating is a Data Definition Language (DDL) statement, while DELETE is a Data Manipulation Language (DML) statement. Records deleted by the truncate TABLE query statement cannot be restored, so you should be very careful before running the TRUNCATE TABLE query.

Truncating simply unmaps all pages that belong to a table (or partition) and their indexes. This deallocates data pages, reducing resource overhead to record deletions made, as well as the number of locks acquired. Truncating a table does not return free space to the disk; you must perform a SHRINKDATABASE operation for the allocated space to be properly allocated and returned to the disk. In general, TRUNCATE TABLE quickly deletes all records in a table by de-allocating the data pages used by the table.

You can incorporate the TRUNCATE TABLE statement into a module, such as a stored procedure, and grant the appropriate permissions to the module using the EXECUTE AS clause. To truncate all tables in the database, you must first remove all foreign key restrictions, truncate the tables, and then restore all restrictions. No, truncating cannot be used with a WHERE clause. You can delete from where, but this loses the speed advantages of TRUNCATE.

In MySQL, there is no way to truncate with the condition. And since you're considering using the TRUNCATE statement, I'm guessing that's because your table is large. The WITH (PARTITIONS (2, 4, 6 TO) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated. Truncating always deletes all rows from a table, leaving the table empty and the table structure intact, whereas DELETE can conditionally delete if the where clause is used.

Rows removed by the TRUNCATE TABLE statement cannot be restored and the where clause cannot be specified in the TRUNCATE statement. In conclusion, truncating is an efficient way to delete all records from a table quickly and without affecting any of its indexes, triggers or dependencies.