Truncate Table

Go Up to Statement and Function Reference (Language Reference Guide)

Introduction

InterBase 2017 introduced the SQL reserved keyword TRUNCATE. The Truncate Table command allows users and applications to empty the contents of a database table. This feature is useful for tables where rows require frequent deletion. The Truncate Table command performs faster, requires less I/O, and journals and archives much less information than an equivalent DELETE FROM table command. ETL applications or other applications can benefit from the combination of TRUNCATE TABLE with the NO RESERVE SPACE table allocation option when they stage large amounts of data that are deleted after use or moved to a more permanent location such as a history table.

Requirements and Constraints

1. The Truncate Table command obtains exclusive and protected write locks, this can have a visible effect on concurrent transactions that try to access tables being truncated. Although the table is being truncated anyways, all layers of the dependent table tree hierarchy in a TRUNCATE CASCADE are locked, and in a TRUNCATE DEFERRED these locks are held until the transaction terminates.
2. As a consequence of the previous point, users may run Truncate Table commands using a transaction with NO WAIT or a WAIT TIME limit. This way the transaction could rollback the operation if a timeout occurs or retry a limited number of times.
3. It is not possible to truncate system tables, temporary tables, and views. For optimization and performance reasons the engine truncates physically some of these tables types, users don't have access to this functionality. However, users might perceive better performance.
4. External tables can be truncated.
5. It's not possible to track who executed a Truncate Table command. InterBase does not support FOR EACH STATEMENT triggers that enable users to write a triggered action for a Truncate Table command.

How it works

Truncate Table operates at table level rather than at row level, it acts on the stored data inside a table instead of the metadata. Truncate Table deletes all the rows of a table in similar way to a DELETE FROM <table> command, but it doesn't perform row level actions like DELETE triggers, check constrains, and index maintenance. Truncate Table is usually faster than row-level deletion.

The Truncate Table command is not under transaction control. When you empty a table, it is not possible to undo the action even if you roll back the transaction that executed it. Only a point-in-time recovery can recover the data from InterBase journal archives. Truncated tables don't have storage allocated for row data, indexes, or blobs.

The Truncate Table command is sensitive to other tables' foreign key constraints that reference the table being truncated. In it's simplest form, foreign key constrains disallow table truncation. InterBase Truncate Table provides several non-SQL and run-time extensions to override this restriction. This enables a more liberal interpretation of the command enable execution in situations that do not compromise existing foreign key constraints. Although Truncate Table is not under transaction control, it is possible to make it behave as if it were by deferring its execution until after the effects of the transaction in which it is contained have been committed or rolled back.

Topics

• Truncate Table syntax
• Truncate Table privilege
• Truncate Table operation
• Truncate Table errors
• Truncate Table effect on Change Views
• Truncate Table examples
• Truncate Table Tutorial

Advance To:

• Truncate Table syntax