---Delete table structure in SQL statements similarities and differences between Drop,truncate and delete

Refer to others summary, this content only for oneself study

I. Syntax in SQL

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 '

Two. Drop,truncate,delete differences

1.drop (delete table): Delete content and definition, free up space. Simply put the entire table out of the way, it is not possible to add data later, unless you create a new table. The drop statement will delete the structure of the table that is dependent on the constraint (constrain), trigger (trigger), index ( index), the stored procedure/function that relies on the table will be preserved, but its state will change to: invalid.

2.truncate (clears the data in the table): Deletes the content, frees up space but does not delete the definition (preserves the data structure of the table). Unlike the drop, it simply empties the table data. Note: You cannot delete row data, you need to clear the table.

3.delete (delete data from table):d The elete statement deletes rows from the table. The DELETE statement deletes a row from the table every time, and the delete operation for that row is saved as a transaction record in the log for rollback operations.

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.

---Delete table structure in SQL statements similarities and differences between Drop,truncate and delete