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

This article will explain in detail how Truncate Table deletes table contents to avoid generating too many logs, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

The database is operated and recorded frequently every day, and the LOG file will be very large and grow faster, sometimes on 5000m a day, and it is not convenient to delete it once a day. Is there any way to avoid generating logs or not generating logs? I haven't found a way to generate logs yet, but we can avoid generating too many logs by using some commands in normal operations.

For example:

SELECT * into test_2 from book

A total of 50000 records, resulting in more than 10 M log

If it's delete from test_2,

Generate more than 80 M log, which obviously virtually increases the log.

When using delete, we can replace it with truncate:

Truncate table test_2

This can significantly reduce the generation of logs.

Let's introduce the command truncate:

Truncate is a statement in SQL that deletes the contents of a data table. The usage is:

Syntax TRUNCATE TABLE name

Parameters.

Name is the name of the table to truncate or the name of the table whose rows are to be deleted.

The following is an explanation of the usage and principle of Truncate statements in MSSQLServer2000, 2005:

Truncate table table names are fast and efficient because:

TRUNCATE TABLE is functionally the same as a DELETE statement without a WHERE clause: both delete all rows in the table. However, TRUNCATE TABLE 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. TRUNCATE TABLE deletes the data by releasing the data pages used to store the table data, and only records the release of the pages in the transaction log.

TRUNCATE TABLE deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on remain the same. The count value used for the new row identity is reset to the seed of the column. If you want to keep the identity count value, use DELETE instead. Use the DROP TABLE statement if you want to delete the table definition and its data.

For tables referenced by FOREIGN KEY constraints, you cannot use TRUNCATE TABLE, but instead use DELETE statements without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate the trigger.

TRUNCATE TABLE cannot be used for tables that participate in indexed views.

When you add data to a table that deletes data with TRUNCATE TABLE, you use UPDATE STATISTICS to maintain index information.

If there is a ROLLBACK statement, the DELETE operation is undone, but the TRUNCATE is not undone.

Exampl

The following example deletes all data in the authors table.

TRUNCATE TABLE authors

Authority

TRUNCATE TABLE permissions are granted by default to table owners, members of the sysadmin fixed server role, and members of the db_owner and db_ddladmin fixed database roles and are not transferable.

Supplementary parameters:

TRUNCATE TABLE name [DROP/REUSE STORAGE]

DROP STORAGE: explicitly indicates the space where data tables and indexes are freed

REUSE STORAGE: explicitly indicates that the space for data tables and indexes is not released

This is about how Truncate Table deletes table contents to avoid generating too many logs. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.