Shulou(Shulou.com)05/31 Report--

How to use truncate and delete sentences in sqlserver. In view of this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

Truncate

Place of haunt: truncate table tb

Trick: delete content, free up space, but not delete definitions. Unlike drop, he is just emptying the table data, he is gentle.

It is also a class, he only removes all the students. The class is still there, the job is still there, and if there are new students who can go in, they can also be assigned to the position.

Deleting content is easy to understand, and it is also easy to understand without deleting the definition, which is to retain the data structure of the table.

The picture above shows: delete content

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

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

Right: Id column identity column. Due to the previous delete row data, the identity column is discontiguous (reflecting that delete deletion does not free up space)

Add three more pieces of data after truncate table Teacher

Right: the same Id is the identity column. When you insert data, the identity column is continuous (reflecting that truncate deletion is to free up space).

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

Delete

Occasion: delete table tb-although it also deletes the data of the entire table, the process is painful (the system deletes line by line, which is less efficient than truncate)

Or

Delete table tb where condition

Trick: delete the content, do not delete the definition, do not release space. The most easily bullied of the three brothers

Then I won't elaborate on delete, as we all know.

A little summary about 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 fewer system and transaction log resources.

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

1. Truncate is very fast on all kinds of watches, big ones and small ones. If there is a ROLLBACK command, Delete will be undone, but truncate will not be undone.

2. Truncate is a DDL language, and like all other DDL languages, it will be implicitly submitted and cannot use the ROLLBACK command on truncate.

3. Truncate will reset the high level and all indexes. When fully browsing the entire table and index, the table after truncate operation is much faster than the table after Delete operation.

4. Truncate cannot trigger any Delete triggers.

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

6. Cannot empty the parent table

This is the answer to the question about how to use truncate and delete sentences in sqlserver. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.