The article will overview the TRUNCATE TABLE statement in SQL on particular examples and describe the differences between the TRUNCATE, DELETE, and DROP statements.
When you use the TRUNCATE TABLE statement, it simply removes data from the table but keeps the structure unchangeable, and makes entries about it in a transaction log.
TRUNCATE TABLE syntax in SQL
The TRUNCATE TABLE statement, which removes rows from the table, has the following syntax:
TRUNCATE TABLE [schema_name.]table_name
[ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ) ];The following table describes the SQL TRUNCATE TABLE statement parameters.
| Parameter | Description |
| schema_name (optional) | Specify the name of the schema that the table to truncate belongs to. However, you must specify the schema name if the table belongs other than the default schema. |
| table_name | Specify the name of the table, which data you want to remove. |
| WITH PARTITIONS | Specify the number of partitions to truncate. If you use a range, specify the partition numbers separated by TO (for example, WITH PARTITIONS (2 TO 5)). |
Further, we’ll explore the practical usage of the TRUNCATE TABLE statement in SQL and delete the Persons table data from the AdventureWorks2019 database. First, let’s see how many rows the table contains. To do this, we’ll execute the SELECT query with the COUNT(*) function that returns the number of all rows in the table.
SELECT COUNT(*) AS rows_before_table_truncate
FROM Persons;
As you can see, the total number of rows in the Persons table is 50. Next, we truncate the table by executing the TRUNCATE TABLE statement. Afterwards, we retrieve data with the SELECT COUNT(*) query to make sure that the data was deleted:
-- truncate a table
TRUNCATE TABLE Persons;
-- retrieve the number of rows in the table
SELECT COUNT(*) AS rows_after_table_truncate
FROM Persons;
In the result, we see that the rows were deleted.
Now, let’s quickly review the TRUNCATE statement in MySQL, Oracle, or PostgreSQL. The statement works similarly but has some differences. So, let’s check them out.
Oracle TRUNCATE TABLE
In Oracle, the syntax of the TRUNCATE TABLE statement is as follows:
TRUNCATE TABLE [schema_name.]table_name
[ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ]
[ DROP STORAGE | REUSE STORAGE ] ;The following table describes the Oracle TRUNCATE TABLE statement parameters.
| Parameter | Description |
| schema_name (optional) | Specify the name of the schema that the table to truncate belongs to. |
| table_name | Specify the name of the table, which data you want to remove. |
| PRESERVE MATERIALIZED VIEW LOG (optional) | Specify if you want to save the materialized view log. |
| PURGE MATERIALIZED VIEW LOG (optional) | Specify if you want to purify the materialized view log. |
| DROP STORAGE (optional) | Specify if you want all storage for the truncated rows to be deallocated. |
| REUSE STORAGE (optional) | Specify if you want all storage for the truncated rows to remain allocated to the table. |
To illustrate the usage of TRUNCATE TABLE in practice, we’ll are going to apply dbForge Studio for Oracle, which is a fully-functional Oracle IDE with a convenient graphical user interface that simplifies our tasks.
If we execute TRUNCATE TABLE, as shown in the screenshots, and get the total number of rows for the EMPLOYEE table, we receive the following result:
Step 1. Receive data before executing the TRUNCATE TABLE statement
Step 2. Truncate the table and then retrieve the data
In the result, we see that TRUNCATE TABLE deleted rows in bulk from the EMPLOYEE table. Thus, you don’t need to create a table anew – just insert data in the table for further use, if required.
MySQL TRUNCATE TABLE
In MySQL, the TRUNCATE statement drops and recreates the table and has the following syntax:
TRUNCATE [database_name.]table_name;The following table describes the MySQL TRUNCATE TABLE statement parameters.
| Parameter | Description |
| database_name | Specify the name of the database that the table to truncate belongs to. |
| table_name | Specify the name of the table, which data you want to remove. |
Keep in mind that the InnoDB and NDB tables with foreign key references to another table cannot be truncated.
In the example, we truncate the persons table and retrieve the number of rows by executing the TRUNCATE TABLE statement and then the SELECT statement with the COUNT(*) function. We again refer to the dbForge Studio, but now it is the Studio for MySQL that is designed for performing all database-related tasks on MySQL and MariaDB.
Step 1. Receive data before executing the TRUNCATE TABLE statement
Step 2. Retrieve rows from the truncated table
When we compare these two screenshots, we’ll see that the table was emptied successfully.
PostgreSQL TRUNCATE TABLE
In PostgreSQL, the syntax of the TRUNCATE TABLE statement is as follows:
TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ];The following table describes the PostgreSQL TRUNCATE TABLE statement parameters.
| Parameter | Description |
| name | Specify the name of the table to truncate. |
| RESTART IDENTITY | Specify if you want PostgreSQL to automatically restart sequences of columns of the truncated table. |
| CONTINUE IDENTITY | Specify if you do not want to change the values of sequences. |
| CASCADE | Specify if you want all tables with foreign keys, which have references to the mentioned table, to be truncated. |
| RESTRICT | Specify if you do not want to truncate tables with foreign keys, which have references to the mentioned table. |
We truncate the customers table and execute the SELECT query to see whether data was deleted from the table. First, let’s calculate how many rows the table contains. The tool we use to showcase the TRUNCATE TABLE statement work is dbForge Studio for PostgreSQL – you can see it in the screenshot below.
In the result, the row number equals 15. Now, we truncate the table and execute the SELECT statement. It returns 0 rows in the table.
As you can see, you don’t have to delete data row by row and can quickly populate the table anew.
Now, let’s compare DELETE and DROP to TRUNCATE, which are similar but have some distinctions.
Differences between TRUNCATE, DELETE, and DROP
The TRUNCATE statement deletes rows from the table but not the table itself. The DELETE statement only removes table data or rows specified in the WHERE clause. The DROP statement drops the existing database object; for example, it can delete the entire table along with its structure, data, privileges, indexes, constraints, and triggers.
Now, we shortly describe the main differences between TRUNCATE, DELETE, and DROP in the table.
| Parameter | TRUNCATE | DELETE | DROP |
| Language | Data Definition Language Command | Data Manipulation Language Command | Data Definition Language Command |
| Syntax | TRUNCATE table_name; | DELETE FROM table_name; DELETE FROM table_name WHERE condition; | DROP table_name |
| Permissions and privileges required to use the statement |
| DELETE permission |
|
| Removal | All rows in a table | Specific or all rows in a table | Entire table structure, including data, privileges, indexes, constraints, and triggers. |
| Rollback | No | Yes | No |
| Lock | Table lock | Row lock | Table lock |
| Speed | Fastest – it deletes all rows from the table without scanning every row before removing it | Slower than TRUNCATE and DROP – it deletes one or several rows based on the specified condition | Faster than DELETE but slower than TRUNCATE – it first deletes rows from the table and then deletes the table from the database |
| Transaction logging | Entire table | Each row | Entire table |
Note: You should be careful with the TRUNCATE TABLE statement. This command can delete all data permanently. Luckily, dbForge SQL Complete, which is one of the best SQL database development, management, and administration tools, can prevent you from losing data and objects (whether accidentally or not). When you execute DELETE or TRUNCATE statements without specifying the condition in the WHERE clause, SQL Complete analyzes your code, blocks query execution, and displays an execution warning. It suggests you proceed with query execution or cancel it.
Conclusion
In the article, we have shortly described the TRUNCATE, DELETE, and DROP statements and shown examples of how they work. In addition, we have explored the differences between TRUNCATE, DELETE, and DROP. To sum it up:
- To delete specific rows, use the DELETE statement.
- To remove all rows from the table but keep the table structure, use the TRUNCATE statement.
- To delete the entire table with data and structure, use the DROP statement.
For demo purposes, we used dbForge Studios. They are powerful IDEs that are supplied with all features and capabilities to make database development, management, and deployment productive and efficient. Besides these separate Studios, Devart offers the solution to cover tasks on all major DBMSs at once – dbForge Edge, a single IDE for SQL Server, MySQL (and MariaDB), Oracle, and PostgreSQL databases.
Want to try? Download 30-day trial versions of the tools to make sure that they solve all your simple and complex database-related needs.
