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

Although the green Resource network is not recommended to use the command to delete things in the database table, but these delete commands are always useful places.

When it comes to deleting a table's data, the most important thing you can remember is the delete

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

Now to introduce the other two brothers, are deleted table data, but it is also very easy to understand

Boss------Drop

Infestation Occasion: Drop table TB--TB represents the name of the datasheet, hereinafter

Trick: delete content and definition, free space. Simply put the whole table out. It is impossible to add data later, 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 to define

drop table class, which is to remove the entire class. The students and the job are gone.

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

After you execute the following code

Classes table is clear, all right! Removed very violently, as the eldest was well deserved

Dick-----truncate.

Infested occasions: TRUNCATE TABLE TB

Trick: delete content, free space without deleting the definition. Unlike the drop, he just clears the table data, and he's more gentle.

It is also a class, he only removes all the students. The class is still in, the job is still in, if the new student can enter, also can assign the position

Delete Content is easy to understand, do not delete the definition is also easy to understand, is the retention of the table data structure

The image above shows: delete content

After executing the statement, you find that the structure of the datasheet is still in, that is, not deleting the definition

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

Right: ID column identity column, because the row data was deleted before, so there is an identity column discontinuity (which reflects delete delete is not free space)

After TRUNCATE TABLE Teacher, add three more data

Right: The same ID is the identity column, found in the insertion of data, the identity column is continuous (embodies the truncate delete is free space)

Note: Truncate can not delete row data, to delete the table empty

Third-----Delete

Haunt occasions: Delete Table TB-although it is also the data to delete the entire table, but the process is painful (system row by row, truncate less efficient)

Or

Delete Table TB Where condition

Trick: delete Content does not delete the definition, does not free space. Among the three brothers, one of the easiest to bully

And then there's no detail about the delete, 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 the delete operation can be roll back

1, truncate on all kinds of tables, whether large or small are very fast. If a rollback command delete is revoked, the truncate is not revoked.

2, truncate is a DDL language, like all other DDL languages, he will be implicitly submitted, not to truncate use rollback command.

3, TRUNCATE will reset the high-level line and all indexes. When you fully browse the entire table and index, the table after the truncate operation is much faster than the table after the delete operation.

4, TRUNCATE cannot trigger any delete trigger.

5, when the table is emptied after the table and table index is reset to the initial size, and delete is not.

6, can not empty the parent table