SQL statement method to delete all records in a table
How to delete all the records in a table with an SQL statement, share my method here.
Reference Links:
- How do SQL statements remove duplicate records? Four different methods
- How do SQL statements remove duplicate records? Tutorials
TRUNCATE TABLE
Deletes all rows in the table without logging a single row delete operation.
Grammar
TRUNCATE TABLE Name
Parameters
Name
is the name of the table to truncate or the table whose entire row you want to delete.
Comments
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.
The 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.
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. (SQL statement Encyclopedia)
For tables 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.
TRUNCATE table cannot be used for tables that participate in an indexed view.
example, delete all data from the authors table.
TRUNCATE TABLE Authors