Truncate Statement in SQL

Truncate Statement in SQL

Definition: The TRUNCATE statement is a SQL command used to remove all data from a table or a specific partition of a table. It is similar to the DELETE statement, but it is more efficient because it bypasses transaction logging, resulting in faster execution. Truncating a table is a non-recoverable operation, meaning that the data cannot be restored once it is truncated.

Syntax: The syntax for the TRUNCATE statement is as follows:

sql
TRUNCATE TABLE table_name [PARTITION (partition_name)];

Parameters:

• table_name: The name of the table from which you want to remove the data.
• partition_name (optional): If the table is partitioned, you can specify the name of a specific partition to truncate. If omitted, the statement truncates the entire table.

Example: Suppose we have a table named "employees" with the following structure:

sql
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  salary DECIMAL(10,2)
);

To truncate the entire "employees" table, we can use the following TRUNCATE statement:

sql
TRUNCATE TABLE employees;

If we want to truncate a specific partition of a partitioned table, we can specify the partition name as follows:

sql
TRUNCATE TABLE employees PARTITION (p1);

Explanation:

• The TRUNCATE statement removes all rows from the specified table or partition.
• Unlike the DELETE statement, which removes rows one by one and logs each deletion, TRUNCATE simply deallocates the data pages, resulting in faster execution.
• Truncating a table also resets any auto-incrementing identity values or sequence generators associated with the table.
• It is important to note that truncating a table cannot be undone. Once data is truncated, it is permanently removed from the table.

Note:

• The TRUNCATE statement requires the necessary permissions on the table or partition to perform the operation.
• Truncating a table also releases the storage space associated with the table, but it does not release the storage space allocated for the table structure itself. To release the table structure space, you can use the DROP TABLE statement.

Remember to adapt the example and explanation to the specific table and scenario you are working with.