Use of drop, truncate, and delete----Delete table data in SQL statements

First, syntax in SQL

   1, drop Table  table name                            eg:drop table  dbo. Sys_test
   2, truncate table  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 '

Second, Drop,truncate,delete difference

1, Drop (delete table): Delete content and definition, free space. Simply put the whole table out . It is not possible to add data in the future unless a new table is added.

The drop statement will delete the structure of the table that is dependent on the constraint (constrain), the trigger (trigger) index (index), and the stored procedure/function that depends on the table will be preserved, but its state will change to: invalid.

2. Truncate (clears data from table): Deletes content, frees space but does not delete definition ( preserves table's data structure ). Unlike the drop, it just empties the table data.

NOTE: Truncate cannot delete row data, it is necessary to erase the table.

3, delete (delete data from table): DELETE statement deletes rows from table . The DELETE statement performs the deletion by deleting one row from the table at a time and saving the row's delete operation as a transaction record in the log

For a rollback operation.

Truncate and delete without where: delete data only, without deleting the structure of the table (definition)

4. 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 row identity is reset to the seed of the column. If you want to preserve the identity count value, use delete instead.

If you want to delete the table definition and its data, use the DROP TABLE statement.
5. For a table referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE TABLE and you should use a DELETE statement without a WHERE clause. Because TRUNCATE TABLE is logged in the log , it cannot activate the trigger.

6, execution speed, generally speaking: drop> truncate > Delete.

7, the DELETE statement is the database operation language (DML), this operation will be put into rollback segement, after the transaction is committed, and if there is a corresponding trigger, execution will be triggered.

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

Use of drop, truncate, and delete----Delete table data in SQL statements