The truncate TABLE command is a data definition language (DDL) command used to remove all rows from a table. It does not delete the table itself, but rather removes the data stored in the table. The structure of the table, including its columns, constraints, indexes, etc., are maintained. To delete the table definition in addition to its data, use the DROP TABLE statement.
Truncate is a much faster operation than DELETE as it does not require row-by-row deletion. Instead, it unassigns the pages used to store the table data and creates an entry for de-mapping these pages in the transaction log. It does not record every row deletion in the transaction log. The TRUNCATE TABLE statement is used to delete all records from a table in SQL Server.
It performs the same function as a DELETE statement without a WHERE clause. As you can see, the TRUNCATE TABLE statement was successfully reversed and all 3 records are still inside test_table. The main difference between the two is that you can undo the DELETE statement if you want, but you may not be able to undo the TRUNCATE TABLE statement in all SQL databases. To remove all records from the customer_data table, you can include the select query before and after the truncation operation. The WITH (PARTITIONS (2, 4, 6 TO) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated.
However, you can use the EXEC TRUNCATE TABLE statement to include the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant the module sufficient permissions. The table owner, members of the sysadmin fixed server role, the database owner, and the DBDDL administrator fixed database roles have TRUNCATE TABLE permissions by default and are not transferable. However, Truncate will remove the entire table structure from the database and you will have to recreate the table if you want to store data. After executing a TRUNCATE statement on an existing table, it is emptied and therefore does not contain any row records. As it is also a DDL statement, it does not require confirmation at every step but instead automatically triggers confirmation at the end of its execution.
