The truncate TABLE command is used to delete all data from an existing table. It is a data definition language (DDL) command that removes all rows from a table, but the structure of the table and its columns, constraints, indexes, etc. remain intact. To delete the table definition in addition to its data, use the DROP TABLE statement.
Truncate is faster than a DELETE statement without a WHERE clause and does not affect any of the table's indexes, triggers, or dependencies. When the TRUNCATE TABLE statement is issued, the database system deletes all rows from the table by deallocating the data pages allocated by the table. It also makes an entry for page deallocation in the transaction log and does not record deletion of each row in the transaction log. 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. 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.
To truncate a partitioned table, the table and indexes must be aligned (partitioned in the same partition function). You can also use the WITH (PARTITIONS (2, 4, 6 TO) syntax to truncate a specific partition or range of partitions. Now you need to know how to use the TRUNCATE TABLE statement to delete all data from a large table quickly and understand the differences between the TRUNCATE TABLE and DELETE statements. To delete all records from a table in SQL Server, use the SQL TRUNCATE TABLE command. You can also include a select query before and after the truncation operation to verify that all records have been deleted.
Leave Reply