The SQL truncate TABLE statement is a data definition language (DDL) command used to delete all records in a table. It performs the same function as a DELETE statement without a WHERE clause, removing all rows from a table while leaving the structure of the table and its columns, constraints, indexes, etc. intact. To delete the table definition in addition to its data, use the DROP TABLE statement.
SQL Truncate is an efficient way to delete data from an existing table. It does this by deallocating pages, making an entry for page deallocation in the transaction log, and not recording the deletion of each row in the transaction log. This is a recursive operation that will truncate all child tables, granchild tables, etc., using the specified options. The WITH (PARTITIONS (2, 4, 6 TO) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated.
Let's explore the difference between the Delete SQL command and the SQL Truncate command. The Delete command removes rows one at a time and records each deletion in the transaction log. This makes it slower than Truncate and can cause performance issues if there are many rows to delete. Truncate deletes all rows in a table by unallocating the pages that are used to store table data.
It does not record the deletion of each row in the transaction log and is therefore much faster than Delete. 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. From the above statement, partitions 2, 4, 6, 7, 8 will be truncated leaving the data of the other partitions will not be truncated. Deleting rows with the TRUNCATE TABLE statement can be more efficient than deleting and re-creating a table. It also sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process. The MATERIALIZED VIEW LOG clause allows you to specify whether a materialized view record defined in the table should be retained or purged when the table is 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.
Therefore, if your system needs to remove the trigger in order for it to fire, the TRUNCATE TABLE statement should not be issued.
