Truncate Table is a powerful command in SQL that allows users to delete all records from a table without recording deletions of individual rows. It is similar to the DELETE statement without a WHERE clause, but it is faster and uses fewer system log and transaction resources. The TRUNCATE TABLE statement is used to delete all records from a table in SQL Server, resetting the table to zero entries. It performs the same function as a DELETE statement without a WHERE clause, and removes data from a table, but not the table itself.
The DELETE statement deletes rows one at a time and inserts an entry in the transaction log for each deleted row, while the TRUNCATE TABLE statement removes the data by de-allocating the data pages used to store the table data and inserts only the page deallocations into the transaction logs. The TRUNCATE TABLE statement in SQL works by zeroing a file in the database, that is, after executing a TRUNCATE statement on an existing table, the table is emptied and therefore does not contain any row records. Microsoft SQL Server has the ability to delete or truncate tables that have more than 128 extensions without maintaining simultaneous locks on all extensions required for deletion. TRUNCATE TABLE removes all rows from a table, but the structure of the table and its columns, constraints, indexes, etc.
remain. To delete 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. After executing this command, the CUSTOMERS table is truncated and the output of the SELECT statement will be as shown in the code block below -.
TRUNCATE in the Data Definition Language (DDL) is equivalent to DELETE in the Data Manipulation Language (DML). To delete the table definition in addition to its data, you can use the DROP TABLE statement. TRUNCATE TABLE in SQL is a data definition language (DDL) statement that flushes an existing table by deleting all records and preserving columns, privileges, indexes, views, constraints, relationships, etc. stored procedure and grant appropriate permissions to the module using the clause EXECUTE AS.
In this example, if the table to be truncated has an identity column, then its counter for that column is reset to its initial value when the TRUNCATE TABLE statement deletes the data but not when using DELETE statement. In truncate in SQL unlike drop-down table which removes entire table structure it keeps table structure for future use. Since TRUNCATE will not consume large transaction log resources, it will reduce size of transaction log backup file which will improve performance of recovery options such as duplication and transfer of records.
Leave Reply