The SQL TRUNCATE TABLE command is a data definition language (DDL) command used to delete all rows from a table. It performs the same function as a DELETE statement without a WHERE clause, but it is faster and uses fewer system log and transaction resources. The TRUNCATE TABLE command removes data from a table, but not the table itself. It maintains the table structure for future use, and it can be used to delete or truncate tables with more than 128 extensions without maintaining concurrent locks on all extensions required for deletion.
The “-s” option can be used to delete the contents of files, leaving them as zero-byte files on disk. To undo the DELETE statement, you can use the TRUNCATE TABLE statement in all SQL databases. 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. The WITH (PARTITIONS (2, 4, 6 TO) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated.
Truncating a table can be faster and doesn't affect any of the table's indexes, triggers, and dependencies. However, Truncate will remove the entire table structure from the database and you will have to recreate the table if you want to store data. To remove 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 within a module as a stored procedure and grant the appropriate permissions to the module using the EXECUTE AS clause.
