Usage of drop, truncate, and delete statements in sqlserver, sqlservertruncate

Usage of drop, truncate, and delete statements in sqlserver, sqlservertruncate

Although it is not recommended that you use commands to delete items in database tables, these delete commands are useful.

When talking about the keywords used to delete table data, you may remember that the most important thing is delete.

However, we develop databases and read database data, which is less useful to the other two brothers.

It is easy to understand that the other two brothers Delete table data.

Boss ------ drop

Out of Box: drop table tb -- tb indicates the name of the data table, the same below

Tip: Delete content and definitions and release space. Simply putRemove the entire table. It is impossible to add new data later, unless a new table is added,

For example, a class is a table, a student is the data in the table, and a student's role is the definition.

Drop table class is to remove the entire class. The students and positions disappear.

For example, the following TestSchool database has two [Classes] and [Teacher] tables.

After executing the following code

The Classes table is clear! The deletion is very violent, and it is named as the boss.

Secondary ----- truncate

Occasion: truncate table tb

Tip: Delete the content, release the space, but do not delete the definition. Unlike drop, it just clears table data, which is gentle.

It is also a class. He only removes all the students. The class is still in progress and his position is still in progress. If there are new students, he can enter or assign a post.

The deletion content is easy to understand, and it is easy to understand without deleting the definition, that is, the data structure of the table is retained.

This shows: deleting content

After the statement is executed, the data table structure is still found, that is, the definition is not deleted.

As for space release, let's look at the two figures below. You will understand.

Figure right: Id column. Because row data is deleted before, the Id column is not consecutive (this shows that delete does not release space)

After truncate table Teacher, add three more data items.

Figure right: the Id column is the same as the Id column. when data is inserted, the Id column is consecutive (indicating that truncate deletion is to release space)

Note: truncate cannot delete row data. to delete the data, clear the table.

Laosan ----- delete

Out-of-the-box: delete table tb-although it is also used to delete data from the entire table, the process is painful (the system deletes data row by row, which is less efficient than truncate)

Or

Delete table tb where Condition

Tip: Delete the content without deleting the definition or releasing the space. One of the three siblings is the easiest to bully

The delete statement is not described in detail.

Summary of truncate:

The truncate table function is the same as the delete statement without the 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 a row at a time and records one row in the transaction log. Therefore, you can roll back the delete operation.

1. truncate is very fast on various tables, whether large or small. If the ROLLBACK command Delete is used, the truncate command is not used.
2. truncate is a DDL language. Like all other DDL languages, it will be implicitly submitted and cannot use the ROLLBACK command for truncate.
3. truncate will reset the high horizontal line and all indexes. When you completely 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. After the table is cleared, the index values of the table and table are reset to the initial size, while the delete value is not.
6. The parent table cannot be cleared.

Who can tell me what is the difference between Drop, DELETE, and TRUNCATE statements in SQL server?

Drop to delete tables and databases
DELETE a record in a table
TRUNCATE clears the entire table record (unlike deleting all records, it is equivalent to deleting the table and then re-creating the table according to the structure, and all information is initialized) without deleting the table structure.

When delete, drop, and truncate table deletion statements are available in the query, what are their differences in use? Hope to give us some advice

The truncate table function is the same as the DELETE statement without the WHERE clause: both DELETE all rows in the TABLE. However, truncate table is faster than DELETE and uses less system and transaction log resources.
The DELETE statement deletes a row at a time and records one row in the transaction log. Truncate table deletes data by releasing the data pages used to store TABLE data, and only records the release of pages in transaction logs.
Truncate table deletes all rows in the TABLE, but the TABLE structure, its columns, constraints, and indexes remain unchanged. The Count value used by the new row ID is reset to the seed of the column.
To retain the ID Count value, use DELETE instead.
To delete TABLE definitions and data, use the drop table statement.