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

I would like to share with you the method of deleting data from the table in the database. I believe most people don't know much about it, so share this article for your reference. I hope you will get a lot after reading this article. Let's learn about it.

Database delete table data method: 1, use "drop table table name" statement to delete; 2, use "truncate table table name" statement to delete; 3, use "delete from table name where column name = value" statement to delete.

There are three ways for a database to delete data from a table:

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'

Drop,truncate,delete distinction

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.

The above is all the contents of the method of deleting the data from the table in the database. Thank you for your reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.