Usage of truncate in SQL

Transferred from: http://www.studyofnet.com/news/555.html

This article follows: The method of deleting data in a table is delete,truncate, where TRUNCATE TABLE is used to delete all rows in the table without logging a single row delete operation. TRUNCATE table is similar to a DELETE statement without a WHERE clause; However, TRUNCATE table is faster and uses fewer system and transaction log resources. The following describes the use of truncate in SQL

When you no longer need the table, use drop when you still want to keep the table, but to delete all records, use truncate; When you want to delete some records (always with a WHERE clause), use Delete.

Truncate is a SQL syntax that can quickly erase all data in a table. It can also do the function of counting reset zero recalculation for fields with automatic increment.

First, truncate grammar

[{database_name. [Schema_name]. | Schema_name. } ]
table_name
[ ; ]

Parameters

database_name
The name of the database.

Schema_name
The name of the schema to which the table belongs.

table_name
The name of the table to truncate, or the name of the table whose entire row you want to delete.

Second, truncate use precautions

1. 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 less system and transaction log resources. 2. Delete statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data page used to store the table data, and records the release of the page only in the transaction log. 3. TRUNCATE table deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on, remain unchanged. The count value used for the new row identity is reset to the seed of the column. If you want to preserve the identity count value, use DELETE instead. If you want to delete the table definition and its data, use the DROP table statement. 4. For a table referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table and you should use a DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not recorded in the log, it cannot activate the trigger. 5. TRUNCATE table cannot be used for tables that participate in an indexed view. 6. When adding data to a table that uses TRUNCATE table to delete data, use UPDATE statistics to maintain index information. 7. If there is a rollback statement, the delete operation will be revoked, but truncate will not be revoked. Third, you cannot use TRUNCATE table for the following tables
1. The table referenced by the FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.) ）
2. The table participating in the indexed view.
3. A published table by using transactional replication or merge replication.
4. For a table with one or more of the above characteristics, use the DELETE statement.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not record individual row deletions. Four, TRUNCATE, Drop, delete difference
1.drop and delete simply delete the table's data (definition), the drop statement will delete the table's structure, dependent constraints (constrain), triggers (trigger), indexes (index), and stored procedures/functions that depend on the table will be preserved. But becomes invalid state.

The 2.delete statement is a DML language, which is placed in the rollback segement and is not effective until the object is submitted, and is triggered when the trigger (trigger) is executed. Truncate, drop is the DDL language, the operation is effective, the original data will not be placed in the rollback, can not be rolled back, the operation will not trigger trigger.

The 3.delete statement does not affect the extent that the table occupies, and the high watermark (Hi Watermark) remains in its original position. The drop statement frees all the space occupied by the table. The TRUNCATE statement releases space to the extent of minextents by default, unless reuse storage is used. Truncate will reset the high watermark (back to the original).

4. Efficiency: Drop > Truncate > Delete

5. Security: Careful use of drop and truncate, especially when there is no backup, want to delete some data can use delete need to take WHERE clause, rollback segment is big enough, want to delete table can use drop, want to keep table just want to delete all the data of the table, If you have nothing to do with things you can use truncate, if it is related to things, or want to trigger trigger, or delete, if it is to tidy up the internal fragments of the table, you can use truncate to follow reuse Stroage, and then re-import, insert data.

6.delete is a DML statement and is not automatically committed. Drop/truncate are DDL statements that are automatically committed after they are executed.

7, drop is generally used to delete the whole data such as table, mode, index, view, integrity limit, etc. delete is used to delete local data, such as a tuple in a table

8, drop the table structure is deleted; Delete just clear the data.

9. When you no longer need the table, use drop; When you still want to keep the table, but to delete all records, use truncate; When you want to delete some records (always with a WHERE clause), use Delete.

Usage of truncate in SQL