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

It is believed that many inexperienced people don't know what to do about how to delete all the data in the table in TRUNCATE. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Note compared with DELETE statements, TRUNCATE TABLE has the following advantages: less transaction log space is used. The DELETE statement deletes one row at a time and records an entry in the transaction log for each row deleted. TRUNCATE TABLE deletes the data by releasing the data page used to store the table data, and only the page release is recorded in the transaction log. Fewer locks are usually used. When the DELETE statement is executed with a row lock, the rows in the table are locked for deletion. TRUNCATE TABLE always locks tables and pages, not rows. Without exception, no pages will be left in the table. After the DELETE statement is executed, the table still contains empty pages. For example, at least one LCK_M_X table lock must be used to free empty tables in the heap. If a table lock is not used when performing a delete operation, the table (heap) will contain many empty pages. For indexes, the delete operation leaves some empty pages, although these pages are quickly released by the background cleanup process. TRUNCATE TABLE deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on remain the same. To delete a table definition and its data, use the DROP TABLE statement. If the table contains an identity column, the counter for that column resets to the seed value defined by the column. If no seed is defined, the default value of 1 is used. To keep the identity counter, use DELETE. The restriction cannot use TRUNCATE TABLE for the following tables: tables referenced by FOREIGN KEY constraints. The table participating in the indexed view. Replicate published tables by using transactional replication or merge. For tables with one or more of the above characteristics, use the DELETE statement. TRUNCATE TABLE cannot activate the trigger because the operation does not record individual row deletions.

After reading the above, do you know how to delete all the data in the table in TRUNCATE? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.