The SQL truncate TABLE statement is used to delete all records from a table. It performs the same function as a DELETE statement without a WHERE clause, removing all rows from the table but leaving the structure and columns intact. To delete the table definition in addition to its data, use the DROP TABLE statement. Truncate is a data definition language (DDL) command that deallocates pages used to store table data, making an entry for page deallocation in the transaction log.
It does not record the deletion of each row in the transaction log. The TRUNCATE TABLE statement is used to delete all records from a table in SQL Server. It is similar to the DELETE statement without a WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system log and transaction resources. To delete all records from a table, you can include the select query before and after the truncation operation.
You can also 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. The main difference between DELETE and TRUNCATE TABLE is that you can undo the DELETE statement if you want, but you cannot undo the TRUNCATE TABLE statement. The WITH (PARTITIONS (2, 4, 6 TO) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated. Microsoft SQL Server has the ability to delete or truncate tables that have more than 128 extensions without maintaining simultaneous locks on all extensions required for deletion. TRUNCATE TABLE permissions are default for the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.
As you can see, the TRUNCATE TABLE statement was successfully rolled back and all 3 records are still inside test_table.
