TRUNCATE TABLE in SQL

TRUNCATE TABLE is a command that is used to remove all rows from a table without removing the table itself. This command is faster than the DELETE command as it does not log individual row deletions.

Syntax

The basic syntax for the TRUNCATE TABLE command is as follows:

TRUNCATE TABLE table_name;

Example

Let's consider a table named "employees". To truncate this table, we will use the following command:

TRUNCATE TABLE employees;

Output

When the TRUNCATE TABLE command is executed, it deletes all the rows in the specified table. It doesn't return any data after the command is executed.

Explanation

TRUNCATE TABLE is a faster way of removing all the rows from a table than using the DELETE command. When we execute the TRUNCATE TABLE command, it deallocates all the data pages of the table thereby making the table empty. This command also resets the identity value of the table to the seed value of the column.

Use

TRUNCATE TABLE is mainly used to remove all the rows from a table when we don't require the data in the table anymore but we still need the structure of the table for future use. This command is also useful when we want to free up the space occupied by the table.

Important Points

1. The TRUNCATE TABLE command cannot be rolled back. Once the command is executed, the data is gone forever.
2. All constraints and indexes are dropped when a table is truncated. These constraints and indexes must be manually recreated after the table is truncated.
3. TRUNCATE TABLE rarely saves space compared to DELETE FROM table_name due to logging overhead in DELETE statements.

Summary

TRUNCATE TABLE is a useful command in SQL that helps delete all rows from a table quickly and efficiently. It is mainly used when we need to keep the structure of the table for future use, but we don't require data anymore. However, it is important to be aware of the constraints and indexes that are dropped when TRUNCATE TABLE is executed.