Shulou(Shulou.com)06/01 Report--

This article is about how to write sql to delete table data. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

1. Delete 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, the difference between drop,truncate,delete

1. Drop (delete table): delete content and definition to free up space. To put it simply, get rid of the whole watch. It is impossible to add new data in the future unless you add a new table.

The drop statement removes the constraint (constrain) on which the structure of the table is dependent (constrain), the trigger (trigger) index (index); stored procedures / functions that depend on the table will be retained, but their state will change to: invalid.

2. Truncate (emptying the data in the table): delete the content, free up space without deleting the definition (retain the data structure of the table). Unlike drop, it just empties the table data.

Note: truncate cannot delete row data. If you want to delete it, you have to empty the table.

3. Delete (delete data from the table): the delete statement is used to delete rows from the table. The delete statement performs a delete by deleting a row from the table each time, while saving the deletion of that row in the log as a transaction record

To perform a rollback operation.

Truncate and delete without where: delete data only, not table structure (definition)

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

Use the drop table statement if you want to delete the table definition and its data.

5. Instead of using truncate table for tables referenced by foreign key constraints, you should use a delete statement without a where clause. Because truncate table is recorded 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 not take effect until the transaction is committed in rollback segement. If there is a corresponding trigger, it will be triggered when it is executed.

Truncate and drop are database definition languages (ddl). The operation takes effect immediately. The original data is not put into rollback segment, cannot be rolled back, and the operation does not trigger trigger.

Thank you for reading! On the deletion of table data how to write sql to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.