Drop/delete/truncate table

Note: The delete statement is a delete statement without the WHERE clause.
Similarities
Truncate, delete without WHERE clause, and drop both delete table data.
Differences:
1. truncate and delete: delete only data. Do not delete the table structure (Definition)
The drop statement will delete the constraints, triggers, and indexes that the table structure is dependent on. Stored Procedures/functions that depend on the table will be retained, but it changes to the invalid status.
2. The delete statement is DML, which is put into the rollback segement and takes effect only after the transaction is committed. If a trigger exists, it is triggered during execution.
Truncate, drop is DDL, and the operation takes effect immediately. The original data is not stored in rollback segment, and cannot be rolled back. Trigger is not triggered.
3. The delete statement does not affect the extent used by the table, and the high watermark keeps the original position unchanged.
Obviously, the drop statement releases all the space occupied by the table.
The truncate statement is released to the minextents extent by default, unless reuse storage is used; The truncate will reset the high water line (back to the beginning ).
4. Speed, in general: Drop>; truncate>; Delete
5. Security: Be careful when using drop and truncate, especially when there is no backup. Otherwise, it will be too late to cry.
To delete some data rows, use Delete. Note that the WHERE clause should be included. The rollback segment should be large enough.
To delete a table, use drop
Delete all data if you want to keep the table. If it is not related to the transaction, use truncate. If it is related to the transaction or you want to trigger the trigger, use Delete.
If you want to organize fragments in the table, you can use truncate to keep up with the reuse Stroage, and then re-import/insert data.

The difference in truncate and delete in SQL Server

I 've answered this question wrong times, and answered it again this weekend. What is the difference when doingDelete tableaInsteadTruncate tablea? A common misconception is that they do the same thing. Not so. In fact, there are using differences between the two.

Delete is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted.

You can delete any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.

Truncate is also a logged operation, but in a different way. truncate logs the deallocation of the data pages in which the data exists. the deallocation of Data Pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. this is what makes truncate a faster operation to perform over Delete.

You cannot truncate a table that has any foreign key constraints. You will have to remove the contraints, truncate the table, and reapply the contraints.

truncate will reset any identity columns to the default seed value. this means if you have a table with an identity column and you have 264 rows with a seed value of 1, your last record will have the value 264 (assuming you started with value 1) in its identity columns. after truncateing your table, when you insert a new record into the empty table, the identity column will have a value of 1. delete will not do this. in the same scenario, if you deleted your rows, when inserting a new row into the empty table, the identity column will have a value of 265.