Truncating is a much faster and more efficient way to delete all records from a table than using the DELETE command. It does not activate triggers, uses less of the transaction log, and does not affect the identifier of the data object. However, it is not possible to use truncation when a foreign key references a table or when tables are used in replication or with indexed views. The TRUNCATE command is an operation of the data definition language and is used to delete all the records in a table.
It is like a DELETE command without the WHERE clause with a much smaller safety net. It is preferable to use TRUNCATE instead of DELETE if you want to delete all the records in a table, since it is faster than the second. The structure or schema of the table is preserved, but it will assign a new data object identifier unless there has never been an insert in the table since its creation. Even a single insertion that is undone will cause a new data object identifier to be assigned by truncating it.
In SQL Server, you can undo a truncation operation if it is within a transaction and the transaction has not been committed. Truncate will work on the data in the original database, but if you delete it, it will work on the copy of the original object, it will make changes to the database once you have received the confirmation instruction from the user. The problem is that not many people know that truncating can be undone, so they may consider an answer incorrect if you tell them that yes, truncating can be undone. However, I have seen it unintentionally truncated, breaking referential integrity and violating other restrictions.
Because TRUNCATE TABLE is a DDL (Data Definition Language) command, not a DML (Data Manipulation Language) command, delete triggers are not executed.
Leave Reply