Truncate is a SQL command used to delete all records from a table. It performs the same function as a DELETE statement without a WHERE clause, and is faster and uses fewer system log and transaction resources. It places a table and page lock to delete all records, and requires less space in the transaction log than the delete command. Truncate cannot be used on a table that has foreign key references from other tables, unless all those tables are also truncated in the same command.
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. The TRUNCATE TABLE statement is used to remove 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. It is important to note that 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. At a high level, you might consider the truncate command to be similar to a DELETE command without a WHERE clause.
However, it is important to note that if ON TRUNCATE triggers are defined for any of the tables, all BEFORE TRUNCATE triggers are fired before any truncation occurs and all AFTER TRUNCATE triggers are fired after the last truncation is performed and sequences are reset. The main difference between the DELETE statement and the TRUNCATE TABLE statement 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. The Delete command is slower than the Truncate command and requires more space in the transaction log than the truncate command. To truncate a partitioned table, the table and indexes must be aligned (partitioned in the same partitioning function). The SQL TRUNCATE TABLE command is used to delete complete data from an existing table. The Delete command is useful for deleting all or certain rows from a table specified by a Where clause.
The truncate command deletes all rows from a table. 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. After truncation, the table will be empty for concurrent transactions, if they use a snapshot taken before the truncation occurred.
Leave Reply