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

While it's not recommended to use commands to delete things in a database table, these delete commands are always useful.

When it comes to deleting table data, the biggest thing you can remember is the delete.

However, we do database development and read database data. It's less for the other two brothers.

Now to introduce the other two brothers, is to delete the table data, in fact, it is very easy to understand

Boss------Drop

Scenario: Drop table TB--TB represents the name of the data table

Trick: delete content and definitions and free up space. Simply put the whole table out . It is not possible to add data in the future, unless you add a table,

For example: A class is a table, the student is the data in the table, the student's job is the definition

drop table class, which is to remove the entire class. Students and jobs are gone

For example, the following Testschool database has two tables [Classes] table and [Teacher] Table

After you execute the following code

The classes table is clear! Deleted very violent, as the boss deserved

Dick-----Truncate

Haunt Occasion: TRUNCATE TABLE TB

Trick: delete content, free space, but not delete definition. Unlike the drop, he just empties the table data, he is more gentle.

Also a class, he only removes all the students. Class is still in, if there are new students can go in, also can be assigned to the job

Deleting content is easy to understand, it is easy to understand without deleting the definition, which is to preserve the data structure of the table

It shows: delete content

After executing the statement, it is found that the structure of the data table is still there, that is, not deleting the definition

As for releasing space, look at the following two pictures. You'll understand.

Right: ID column identity column, because the previous delete row data, so the identity column will appear discontinuous (reflecting delete delete is not free space)

Three additional data after TRUNCATE TABLE Teacher

Right: The same ID is the identity column, and when the data is inserted, the identity column is contiguous (reflecting the truncate delete is the free space)

Note: Truncate can not delete row data, you need to erase the table

Old Three-----Delete

Haunt occasions: Delete Table TB-although it is also the data to delete the entire table, but the process is painful (the system is deleted on one line, efficiency is lower than truncate)

Or

Delete Table TB Where condition

Trick: delete Content does not delete the definition, does not free space. One of the three brothers is the most easily bullied.

And then the delete is not explained in detail, and we all know it.

A small summary of truncate:

TRUNCATE TABLE is functionally the same as a DELETE statement without a WHERE clause: Both delete all rows in the table.

However, truncate is faster than delete and uses less system and transaction log resources.

The DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. So you can roll back the delete operation

1, truncate on various tables whether large or small are very fast. If there is a rollback command delete will be revoked, and truncate will not be revoked.

2. Truncate is a DDL language, and as with all other DDL languages, he will be implicitly committed and cannot use the rollback command with truncate.

3. Truncate will reset the high-level line and all indexes. When a full table and index are fully browsed, the table after the truncate operation is much faster than the table after the delete operation.

4. Truncate cannot trigger any delete triggers.

5. When the table is emptied, the index of the table and table is reset to the initial size, and delete cannot.

6. Cannot empty the parent table

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