TRUNCATE TABLE

Note: TRUNCATE TABLE is a Data Manipulation Language (DML) statement that removes all rows from a table, but the table structure remains intact. It's generally faster than DELETE for removing all rows from a large table.

Description

The TRUNCATE TABLE statement is used to quickly remove all records from a table. Unlike the DELETE statement, TRUNCATE TABLE is not logged for each deleted row, making it a much faster operation, especially for large tables. It deallocates the data pages used by the table, resetting the table to its initial state (as if it were newly created), but without dropping the table itself.

Syntax

TRUNCATE TABLE table_name [WITH ( option_name [ ,...n ] )]

-- Example:
TRUNCATE TABLE Customers;

-- Example with option to preserve identity seed:
TRUNCATE TABLE Orders WITH (TRUNCATE_ONLY); -- This is a hypothetical option for demonstration. Actual options vary by RDBMS.
            

Parameters

• table_name: The name of the table to be truncated.
• WITH ( option_name [ ,...n ] ): Optional. Specifies options that affect the truncation process. The available options depend on the specific database system (e.g., SQL Server, MySQL, PostgreSQL). Common options might include controlling identity seed resetting or logging behavior.

Behavior and Differences from DELETE

• Performance: TRUNCATE TABLE is significantly faster than DELETE FROM table_name when removing all rows. This is because it deallocates data pages rather than processing each row individually.
• Logging: TRUNCATE TABLE minimally logs the deallocation of pages, whereas DELETE logs the deletion of each row. This makes TRUNCATE TABLE a minimally logged operation in many database systems.
• Triggers: TRUNCATE TABLE does not fire DELETE triggers associated with the table. DELETE statements do fire triggers.
• Rollback: In most database systems, TRUNCATE TABLE operations can be rolled back within a transaction. However, the exact behavior can depend on the transaction isolation level and specific database implementation.
• Identity Seed: By default, TRUNCATE TABLE resets the identity seed (auto-increment value) back to its initial value. DELETE does not reset the identity seed.
• Permissions: Typically requires the ALTER permission on the table, in addition to DELETE permissions for a DELETE statement.
• WHERE Clause: TRUNCATE TABLE cannot be used with a WHERE clause. It always removes all rows. DELETE can use a WHERE clause to remove specific rows.

When to Use TRUNCATE TABLE

• To quickly remove all data from a large table before reloading it.
• To reset a table's contents and its identity seed.
• In maintenance scripts where a table needs to be cleared periodically.

Considerations

• TRUNCATE TABLE is a DDL-like operation disguised as DML. It can have implications for transaction logs and recovery.
• Be absolutely certain you want to remove all data, as recovery can be more complex than rolling back a DELETE statement that specifies specific rows.
• Check your specific database system's documentation for exact syntax, options, and behavior.

Example Scenarios

Clearing a staging table:

Before loading new data into a staging table, you might want to clear its previous contents:

BEGIN TRANSACTION;
TRUNCATE TABLE StagingData;
-- Load new data into StagingData here
COMMIT TRANSACTION;
            

Resetting a table with identity:

If you want to start inserting new records with the first identity value again:

TRUNCATE TABLE UserAccounts;
-- New inserts will start with UserID = 1 (assuming it's the starting seed)