TRUNCATE table is functionally the same as a DELETE statement without a Where clause: Both delete all rows in the table. However, TRUNCATE TABLE is faster than Delete and uses less system and transaction log resources.
The Delete statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data page used to store the table data, and records the release of the page only in the transaction log.
TRUNCATE table deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on, remain unchanged. The count value used for the new row identity is reset to the seed of the column. If you want to preserve the identity count value, use Delete instead.
for tables referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table and you should use a Delete statement without a Where clause. Because TRUNCATE TABLE is not recorded in the log, it cannot activate the trigger.
TRUNCATE table cannot be used for tables that participate in an indexed view.
truncate,delete,drop Similarities and differences:
Note: Delete In this case refers to the DELETE statement without a WHERE clause
same point: truncate and delete without a WHERE clause, and drop deletes the data in the table
different points:
1.truncate and delete only delete data without deleting the structure of the table (definition)
The drop statement will delete the structure of the table that is dependent on the constraint (constrain), the trigger (trigger), index, or the stored procedure/function that depends on the table will remain, but becomes invalid state.
The 2.delete statement is DML, which is placed in the rollback segement, which takes effect after the transaction is committed, and is triggered when the corresponding trigger is executed.
Truncate,drop is DDL, the operation takes effect immediately, the original data is not placed in the rollback segment and cannot be rolled back. The operation does not trigger the trigger.
The 3.delete statement does not affect the extent occupied by the table, and the high waterline (W2atermark) remains in its original position.
apparently the drop statement frees all the space occupied by the table.
The truncate statement frees space to minextents extent by default unless reuse storage is used; Truncate will reset the high watermark (back to the beginning).
4. Speed, in general: drop> truncate > Delete
5. Security: Use Drop and truncate carefully, especially when there is no backup. Otherwise, it's too late to cry.
use on:
to delete part of a data row with delete, note the WHERE clause. The rollback segment is large enough.
want to delete the table, of course with drop
You want to keep the table and delete all the data. If it is unrelated to the transaction, use truncate. If it is related to a transaction, or if you want to trigger trigger, use Delete.
If you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage and re-import/Insert Data
The difference between TRUNCATE TABLE and delete in SQL
