How to Use TRUNCATE TABLE in SQL 

The article will overview the TRUNCATE TABLE statement in SQL on practical 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 and use cases

The TRUNCATE TABLE statement, which removes rows from the table, has the following syntax:

-- SQL TRUNCATE statement 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)).

How to TRUNCATE a TABLE in SQL 

In practice, when you truncate a table, you instantly clear its rows while keeping the structure unchanged. Let’s walk through a simple example using the Persons table in the AdventureWorks2019 database. 

First, check how many rows are 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. The SQL query to truncate a table looks like the following. 

-- truncate a table 
TRUNCATE TABLE Persons; 

Afterwards, we retrieve data with the SELECT COUNT(*) query to make sure that the data was deleted. 

-- 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 dbForge 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.

TRUNCATE TABLE in T-SQL 

In SQL Server, the TRUNCATE TABLE command is implemented as a T-SQL statement that quickly removes all rows from a table. Unlike DELETE, it does not log individual row deletions, which makes T SQL truncate operations much faster for large datasets. 

In T-SQL (SQL Server), the TRUNCATE statement follows the same syntax as the standard SQL TRUNCATE TABLE command. 

TRUNCATE TABLE [schema_name.]table_name; 

The SQL Server TRUNCATE operation is defined by strict rules that control its permissions, logging, and rollback behavior. The following table summarizes these rules for quick reference. 

Aspect	Behavior in SQL Server
Permissions	Requires ALTER table permissions.
Rollback	Possible inside a transaction.
Logging	Only deallocates data pages, doesn’t log row deletions.
WHERE clause	Not allowed — TRUNCATE always removes all rows.

For example, truncating a Sales table in T-SQL looks like this: 

TRUNCATE TABLE Sales; 

TRUNCATE vs DELETE vs DROP: Key Differences 

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	TRUNCATE TABLE permission TRUNCATE privilege (in PostgreSQL) ALTER permission (in SQL Server) DROP privilege (in MySQL) DROP ANY TABLE privilege (in Oracle)	DELETE permission	ALTER permission (in SQL Server) DROP privilege (in MySQL) DROP ANY TABLE privilege (in Oracle) Users can drop their own tables (in PostgreSQL)
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 solution 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.

FAQ 

What is TRUNCATE in SQL? 

The TRUNCATE command in SQL removes all rows from a table instantly without logging individual deletions. Unlike DELETE, it keeps the structure intact. 

When should I use TRUNCATE instead of DELETE? 

Use TRUNCATE when you need to clear all rows quickly. Use DELETE when you want to remove specific rows with a WHERE clause. 

Can TRUNCATE be rolled back in SQL Server? 

Yes. If executed inside a transaction, a TRUNCATE operation in SQL Server can be rolled back. 

Does TRUNCATE reset identity columns? 

In SQL Server and PostgreSQL, TRUNCATE can reset identity values back to the seed if you specify the option. Otherwise, identity values continue.