The TRUNCATE TABLE statement is a data definition language (DDL) operation that is used to delete all records from a table in SQL Server. It performs the same function as a DELETE statement without a WHERE clause. The TRUNCATE TABLE command removes data from a table, but not the table itself. The structure of the table and its columns, constraints, indexes, etc.
remain intact. To delete the table definition in addition to its data, you can use the DROP TABLE statement. The TRUNCATE TABLE statement in SQL works by zeroing a file in the database. After executing a TRUNCATE statement on an existing table, the table is emptied and therefore does not contain any row records. It resets the table to zero entries.
The DELETE statement deletes rows one at a time and inserts an entry in the transaction log for each deleted row. On the other hand, the TRUNCATE TABLE statement removes the data by de-allocating the data pages used to store the table data and inserts only the page deallocations into the transaction logs. The table owner, members of the sysadmin fixed server role, database owner, and DBDDL administrator fixed database roles have TRUNCATE TABLE permissions by default and are not transferable. The TRUNCATE option is available on all editions of SQL Server, Azure SQL Server, and Azure datastore instances. It is obvious that TRUNCATE is much faster and uses fewer resources than the DELETE statement in all respects. If you want to truncate a specific partition or range of partitions, you can use the WITH PARTITIONS clause.
However, if your system needs to remove the trigger in order for it to fire, the TRUNCATE TABLE statement should not be issued. Let's create a sample table and fill in some records to demonstrate the different aspects of TRUNCATE using the following T-SQL code. After executing a TRUNCATE statement on an existing table, the table becomes empty and therefore does not contain any row records. In SQL Server, truncating a table is a quick way to clear records in a table if you don't have to worry about rollback. In SQL, the TRUNCATE TABLE statement is a data definition language (DDL) operation that marks extensions of a table for deallocation (empty for reuse). Tables with more than 128 extensions can be deleted or truncated in Microsoft SQL Server without requiring simultaneous locks on all extensions required for deletion. It is obvious that TRUNCATE is much faster and uses fewer resources than the DELETE statement in all respects.
In this example, the employee table is a partitioned table, and the TRUNCATE TABLE statement would truncate partitions 1 through 5, as well as partition 7 in this partitioned table. Now, the CUSTOMERS table is truncated and the output of the SELECT statement will be as shown in the code block below −.Therefore, if you need to quickly clear records from a database table without worrying about rollback or deleting specific partitions or ranges of partitions, then using TRUNCATE is your best bet. It runs faster and uses fewer system and transaction log resources than DELETE.
