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

This article introduces how to use Truncate in SQL. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

1. 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 rows you want to delete.

II. Points for attention in the use of Truncate

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 fewer system and transaction log resources.

2. The DELETE statement deletes one row at a time and records one entry for each deleted row 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.

3. TRUNCATE TABLE deletes all rows in the table, but the table structure and its columns, constraints, indexes, etc., remain unchanged. 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.

4. Instead of using TRUNCATE TABLE for tables referenced by FOREIGN KEY constraints, you should use a DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate the trigger.

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

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

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

You cannot use TRUNCATE TABLE on the following tables

1. Tables referenced by FOREIGN KEY constraints. You can truncate a table that has a foreign key that references itself. )

2. Tables that participate in indexed views.

3. Replicate published tables by using transactional replication or merge.

4. For tables with one or more of the above characteristics, use the DELETE statement.

5. TRUNCATE TABLE cannot activate the trigger because the operation does not record individual row deletions.

IV. The difference between TRUNCATE, Drop and Delete

1.drop and delete simply delete the data (definition) of the table, and the drop statement will delete the structure of the table, dependent constraints (constrain), triggers (trigger), and indexes (index); stored procedures / functions that depend on the table will be retained but become invalid.

The 2.delete statement is in DML language, and this operation is placed in rollback segement and takes effect only after the transaction is submitted; if there is a corresponding trigger (trigger), it will be triggered when it is executed. Truncate and drop are DDL languages, which take effect immediately after the operation. The original data will not be put into rollback, cannot be rolled back, and the operation will not trigger trigger.

The 3.delete statement does not affect the extent and high watermark occupied by the table to keep the original position unchanged. The drop statement frees up all the space occupied by the table. The truncate statement frees space to minextents's extent by default, unless reuse storage is used. Truncate will reset the high waterline (back to the beginning).

4. Efficiency: drop > truncate > delete

5. Security: use drop and truncate carefully, especially when there is no backup. If you want to delete part of the data, you need to use the where clause. If the rollback segment is large enough, you can use drop to delete the table. If you want to delete the table, you can use drop. If you want to keep the table, you can use truncate if it has nothing to do with things. If you want to trigger trigger, you can also use delete. If you want to defragment the table, you can use it. You can use truncate to keep up with reuse stroage, and then re-import and insert data.

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

7. Drop is generally used to delete overall data such as tables, schemas, indexes, views, integrity restrictions, etc. Delete is used to delete local data such as a tuple in a table

8. DROP deletes all the table structures; DELETE just erases 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.

On how to use Truncate in SQL to share here, I hope the above content can be of some help to you, can 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.