When to use TruncateYou want to “reset a table to its empty state”. The DMSS deletes all rows and resets the identity key values to their initial state. You must have a very quick way to clear the data from the table. You want to delete rows from a table without triggering the trigger after deleting from the table.
The SQL truncate TABLE statement is used to delete all records in a table. It performs the same function as a DELETE statement without a WHERE clause. The SQL TRUNCATE TABLE command is used to delete complete data from an existing table. The truncate command deletes all rows from a table.
We can't use a Where clause in this. TRUNCATE TABLE removes all rows from a table, but the structure of the table and its columns, constraints, indexes, etc. remain. To delete the table definition in addition to its data, use the DROP TABLE statement.
To truncate a partitioned table, the table and indexes must be aligned (partitioned in the same partitioning function). If the table is split, all partitions or subpartitions, as well as the LOB and LOB index data segments for each partition or subpartition, are truncated. 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. For a domain index, this instruction invokes the appropriate truncation routine to truncate the domain index data.
Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process. The mytable TRUNCATE TABLE statement is logically (but not physically) equivalent to the DELETE FROM mytable statement (without a WHERE clause). Let's explore the difference between the Delete SQL command and the SQL Truncate command in the following table. If you have specified more than one free list for the object you are truncating, the REUSE STORAGE clause also removes any assignment of free lists to instances and resets the maximum water level mark to the beginning of the first extension.
TRUNCATE TABLE removes the data by de-allocating the data pages used to store the table data and records only page deallocations in the transaction log. However, when the table is truncated, the index is also truncated and a new high watermark is calculated for the index segment. To truncate a table or cluster, the table or cluster must be in its schema or it must have the DROP ANY TABLE system privilege. The main difference between the two is that you can undo the DELETE statement if you want, but you may not be able to undo the TRUNCATE TABLE statement in all SQL databases.
In SQL, the TRUNCATE TABLE statement is a data definition language (DDL) operation that marks extensions of a table for deallocation (empty for reuse). You must truncate the cluster, delete all rows in the table, or delete and recreate the table. The TRUNCATE TABLE statement deletes all rows from a table, but the structure of the table and its columns, constraints, indexes, and so on remain intact.
Leave Reply