How to use Truncate Table statement correctly

This article to share with you is about how to use the correct Truncate Table statement, Xiaobian think it is very practical, so share it with you to learn, I hope you can gain something after reading this article, not much to say, follow Xiaobian to see it.

TRUNCATE TABLE

Delete all rows in a table without logging individual row deletions.

syntax

TRUNCATE TABLE name

parameters

name

Is the name of the table you want to truncate or delete all its rows.

annotation

TRUNCATE TABLE is functionally identical to a Delete statement without a WHERE clause: both delete all rows in the table. TRUNCATE TABLE is faster than Delete and uses fewer system and transaction log resources.

Delete statements delete rows one at a time and record an entry in the transaction log for each row deleted. TRUNCATE TABLE deletes data by freeing the data pages used to store the table data, and only records the page release in the transaction log.

TRUNCATE TABLE deletes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain unchanged. The count value used to identify the new row 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 a table definition and its data, use the DROP TABLE statement.

For tables referenced by FOREIGN KEY constraints, TRUNCATE TABLE cannot be used; instead, a Delete statement without a WHERE clause should be used. TRUNCATE TABLE cannot activate triggers because it is not logged.

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

example

The following example deletes all data from the authors table.

TRUNCATE TABLE authors

permissions

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.

The above is how to use the Truncate Table statement correctly. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.