Thursday, April 18, 2024
HomeProductsSQL Server ToolsTRUNCATE TABLE in SQL

TRUNCATE TABLE in SQL

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.

Download dbForge Studio for SQL Server

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.

ParameterDescription
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_nameSpecify the name of the table, which data you want to remove.
WITH PARTITIONSSpecify 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;
Retrieve the number of rows from the SQL table

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;
Retrieve the number of rows after truncating the SQL table

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.

ParameterDescription
schema_name (optional)Specify the name of the schema that the table to truncate belongs to.
table_nameSpecify 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.

ParameterDescription
database_nameSpecify the name of the database that the table to truncate belongs to.
table_nameSpecify 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.

ParameterDescription
nameSpecify the name of the table to truncate.
RESTART IDENTITYSpecify if you want PostgreSQL to automatically restart sequences of columns of the truncated table.
CONTINUE IDENTITYSpecify if you do not want to change the values of sequences.
CASCADESpecify if you want all tables with foreign keys, which have references to the mentioned table, to be truncated.
RESTRICTSpecify 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.

Retrieve the total amount of rows in the table

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.

Truncate the table and check the number of 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.

ParameterTRUNCATEDELETEDROP
LanguageData Definition Language CommandData Manipulation Language CommandData Definition Language Command
SyntaxTRUNCATE 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)
RemovalAll rows in a tableSpecific or all rows in a tableEntire table structure, including data, privileges, indexes, constraints, and triggers.
RollbackNoYesNo
LockTable lockRow lockTable lock
SpeedFastest – it deletes all rows from the table without scanning every row before removing itSlower than TRUNCATE and DROP – it deletes one or several rows based on the specified conditionFaster than DELETE but slower than TRUNCATE – it first deletes rows from the table and then deletes the table from the database
Transaction loggingEntire tableEach rowEntire 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.

RELATED ARTICLES

Whitepaper

Social

Topics

Products