The difference between drop, truncate, and delete

the difference between drop, truncate, and deleteOriginal November 22, 2015 19:30:21 the difference between drop, truncate, and delete

(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.

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 to table; Delete could be table and view

(5) TRUNCATE and delete Delete only the data, 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.

One, delete

1, delete is DML, a row is deleted from the table every time the delete operation is performed, and the deletion of the row is recorded in the redo and undo tablespace for Rollback (rollback) and redo operations, but note that the table space is large enough to A manual commit operation is required to take effect, and you can undo the operation by rollback.

2. Delete can delete the data in the table that satisfies the condition according to the condition, if you do not specify a WHERE clause, delete all the records in the table.

3. The DELETE statement does not affect the extent that the table occupies, and the high watermark keeps the original position unchanged.

Second, truncate

1, Truncate is DDL, will be implicitly committed, so, can not be rolled back, does not trigger the trigger.

2. Truncate deletes all records in the table and will reset the high watermark and all indexes, releasing space by default to minextents extent unless using reuse storage. Logs are not logged, so execution is fast, but cannot be undone by rollback (if a table is accidentally truncate off, it can be restored, but cannot be recovered by rollback).

3. For a table referenced by a foreign KEY (ForeignKey) constraint, you cannot use TRUNCATE TABLE, but you should use a DELETE statement without a WHERE clause.

4, truncatetable can not be used to participate in the indexed view of the table.

Third, drop

1, drop is DDL, will be implicitly committed, so, can not be rolled back, does not trigger the trigger.

2. The drop statement deletes the table structure and all the data and releases all the space occupied by the table.

3. The DROP statement deletes the constraints on which the table's structure is dependent, triggers, indexes, stored procedures/functions that depend on the table, but becomes invalid state.

Summarize:

1, in the speed, generally speaking,drop> truncate > Delete.

2, in the use of drop and truncate must pay attention to, although you can recover, but in order to reduce the trouble, or to be cautious.

3, if you want to delete some data with delete, pay attention to take the WHERE clause, the rollback segment is large enough;

If you want to delete the table, of course with drop;

If you want to keep the table and all the data deleted, if not related to the transaction, with truncate;

If it is related to a transaction, or if you want to trigger a trigger, or 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.