In the SQL statement ---- delete table data drop, truncate, and delete usage, droptruncate

In the SQL statement ---- delete table data drop, truncate, and delete usage, droptruncate

Reprinted from: http://www.cnblogs.com/1312mn/p/4422396.html

I. SQL syntax

1. drop table name eg: drop table dbo. Sys_Test
2. truncate table name eg: truncate table dbo. Sys_Test
3. delete from table name where column name = value eg: delete from dbo. Sys_Test where test = 'test'

Ii. Differences between drop, truncate and delete

1. drop (delete table): Delete content and definitions and release space. Simply put, it is impossible to remove the entire table and add data later, unless a new table is added.

The drop statement deletes the constrain and trigger indexes of a table. Stored Procedures/functions dependent on the table are retained, but its status changes to: invalid.

2. truncate (clear table data): Delete content, release space, but not delete definitions (retain the table's data structure ). Unlike drop, it only clears table data.

Note: truncate cannot delete row data. to delete the data, clear the table.

3. delete (delete table data): The delete statement is used to delete rows in the table. The delete statement deletes a row from the table each time and saves the row deletion operation as a transaction record in the log for rollback.

Truncate and delete without where: delete data only, but not the table structure (Definition)

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

5. 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 recorded in the log, it cannot activate the trigger.

6. execution speed. Generally, drop> truncate> delete.

7. The delete statement is the database operation language (dml). This operation will be placed in 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.

Top

0

Step on

0

View comments