Detailed explanation of the similarities and differences of drop, delete and truncate in SQL _mssql

First: The same point:

Truncate and delete with no WHERE clause, and drop deletes data in the table

Second: Different points:

1 . Truncate and delete Deletes only the data do not delete the structure of the table (definition)
The DROP statement deletes the table's structure-dependent constraints (constrain), triggers (trigger), indexes (index), and the stored procedures/functions that depend on the table remain, but become invalid states.

2. The DELETE statement is the database manipulation language (DML), which is placed in the rollback segement, which takes effect after the transaction is committed, and is triggered if a corresponding trigger execution occurs.
truncate, drop is the database definition language (DDL), the operation takes effect immediately , the original data is not placed in the rollback segment, 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 high watermark keeps the original position motionless;
Obviously the drop statement frees up all of the space occupied by the table; The TRUNCATE statement, by default, sees space released to minextents extent unless the reuse storage;truncate is used to reset the high watermark (back to the beginning).

4. Speed: Generally speaking: drop> truncate > Delete

5. Security: Be careful to use drop and truncate, especially when there is no backup., otherwise it's too late to cry.

6. Use: to delete some data rows with delete, note with the WHERE clause. The rollback segment should be large enough.

Want to delete the table, of course, with drop.
You want to keep the table and delete all the data, if it's not about the transaction, use truncate, if it's related to the transaction, or if you want to trigger the trigger, or use Delete.
If you are defragmenting the inside of your table, you can use truncate to keep up with reuse stroage and then re-import/insert the data.

The article is very short, do not know if you have the difference between drop, delete and truncate?

To share some details:

(1) The DELETE statement performs a deletion by deleting a row from the table and saving the row's delete operation as a transaction record in the log for rollback operations. TRUNCATE table deletes all the data from the table once and does not log a separate delete action record to the journal Save, and the delete row is unrecoverable. And the delete trigger associated with the table is not activated during deletion. Faster execution.

(2) Space occupied by tables and indexes. When the table is truncate, the space occupied by the table and index is restored to its original size, and the delete operation does not reduce the amount of space occupied by the table or index. The drop statement frees up all the space occupied by the table.

(3) Generally,drop > Truncate > Delete

(4) Application range. TRUNCATE can only be table and view for Table;delete

(5) TRUNCATE and delete delete data only, and drop deletes the entire table (structure and data).

(6) Truncate and no where Delete: delete only data without deleting the structure (definition) of the table the drop statement will delete the constraint (constrain) of the table's structure, the trigger (trigger) index, the stored procedure that depends on the table/ The function will be preserved, but its state will change to: invalid.

(7) The DELETE statement is DML (data maintain Language), which is placed in the rollback segment and is not effective until the transaction is committed. If there is a corresponding Tigger, the execution will be triggered.

(8) Truncate, drop is a DLL (data define language), the operation takes effect immediately, the original data is not placed in the rollback segment, can not be rolled back

(9) Careful use of drop and truncate in the absence of a backup. To delete some data rows with delete and note where to constrain the scope of the impact. The rollback segment should be large enough. To delete a table with drop, if you want to preserve the table and delete the data in the table, you can do it with truncate if it is not a transaction. If it's related to the transaction, or the teacher wants to trigger the trigger, use Delete.

(a) Truncate table name is faster and more efficient because:
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 pages used to store the table data, and only records the release of the page 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 line identification is reset to the seed of the column. If you want to keep the identity count value, use DELETE instead. If you want to delete the table definition and its data, use the DROP table statement.

(12) 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 the TRUNCATE table is not logged in the log, it cannot activate the trigger.

The above is the entire content of this article, I hope to help you learn.