What is the SQL to delete table data?

1, delete grammar in SQL

# 1, Drop Table Table name EG: Drop Table DBO.Sys_test
2, Truncate Table Table name Eg: Truncate Table Dbo. Sys_test Differences between truncate and delete

1. Drop (delete table): Delete content and definitions to release space. To put it simply, delete the entire table. It is impossible to add data in the future unless a new table is added.

The drop statement will delete the constraints and trigger indexes that the table structure depends on; the stored procedures/functions that depend on the table will be retained, but their status will become :invalid.

2. Truncate (clear the data in the table): delete content, release space but do not delete the definition (retain the data structure of the table). Different from drop, it just clears the table data.

Note: truncate cannot delete row data. To delete, the table must be cleared.

3. delete (delete data in the table): The delete statement is used to delete rows in the table. The delete statement performs the deletion process by deleting one row from the table at a time, and at the same time, the deletion operation of the row is saved in the log as a transaction record

so that the rollback operation can be performed.

truncate and delete without where: only delete the data, but not the structure (definition) of the table

4. Truncate table deletes all rows in the table, but the table structure and its columns , constraints, indexes, etc. remain unchanged. The count used to identify new rows is reset to the seed of that column. If you want to retain the identity count value, use delete instead.

If you want to delete the table definition and its data, please use the drop table statement.

5. For tables referenced by foreign key constraints, truncate table cannot be used, but the delete statement without a where clause should be used. Since truncate table is logged, it cannot activate triggers.

6. Execution speed, generally speaking: drop> truncate > delete.

7. The delete statement is a database operation language (dml). This operation will be placed in the rollback segment and will only take effect after the transaction is submitted; if there is a corresponding trigger, it will be triggered during execution.

Truncate and Drop are database definition languages ​​(DDL). The operation takes effect immediately. The original data does not put it in the rollback segment and cannot roll back. The operation does not trigger the Trigger.

The above is the detailed content of What is the SQL to delete table data?. For more information, please follow other related articles on the PHP Chinese website!