Differences between SQL statements for table data truncatedeletedrop Deletion

The difference between SQL-based table data deletion and truncatedeletedrop is that the following content is reproduced: learning to use 1. truncate, delete without where clause, and drop will delete table data. 2. drop and truncate are all DDL statements (Data Definition Language), which are automatically submitted after execution. Differences: 1. truncate and delete: only delete data does not

The difference between SQL delete table data truncate delete drop is the reprinted content: learning to use 1. truncate, delete without where clause, and drop will delete the data in the table. 2. drop and truncate are all DDL statements (Data Definition Language), which are automatically submitted after execution. Differences: 1. truncate and delete: only delete data does not

Difference between SQL statement-based table data truncate delete drop

Posted content: Learning

1. truncate, delete without where clause, and drop all delete table data.

2. drop and truncate are all DDL statements (Data Definition Language), which are automatically submitted after execution.

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 the status changes to invalid.

2. The delete statement is the database operation language (dml). This operation will be placed in the rollback segement and take effect after the transaction is committed. If a trigger exists, it will be triggered during execution.
Truncate and drop are database Definition Language (ddl). 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.
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. Generally, 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
If you want to retain the table and delete all the data, use truncate if it is not related to the transaction. If it is related to a transaction or you want to trigger a trigger, delete is used.
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.

6. delete is a DML statement and is not automatically submitted. Drop/truncate are DDL statements, which are automatically submitted after execution.

7. The truncate table function is the same as the DELETE statement without the 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 a row at a time and records one row in the transaction log. Truncate table deletes data by releasing the data pages used to store TABLE data, and only records the release of pages in transaction logs.

8. truncate table deletes all rows in the TABLE, but the TABLE structure and its columns, constraints, and indexes remain unchanged. The Count value used by the new row ID is reset to the seed of the column. To retain the ID Count value, use DELETE instead. To delete TABLE definitions and data, use the drop table statement.

9. For tables referenced by the foreign key constraint, the truncate table cannot be used, but the DELETE statement without the WHERE clause should be used. Because the truncate table is not recorded in the log, it cannot activate the trigger.

10. truncate table cannot be used for tables that participate in the index view.

Posted on