intro
Let's learn everything you need to know about the SQL TRUNCATE TABLE statement to empty tables like a pro.
Table data can grow large, and there may come a point where you need to start fresh by clearing it. This is especially useful in testing or development environments, where data can quickly become irrelevant. So, how do you clean up a table while keeping its structure intact? The answer is the SQL TRUNCATE TABLE statement!
In this guide, you will learn how to truncate a table in SQL and master this operation across the most popular databases.
Let’s dive in!
What Is the SQL TRUNCATE TABLE Statement?
The SQL TRUNCATE TABLE statement removes all rows from a table. Unlike the DELETE statement, TRUNCATE is classified as an SQL DDL (Data Definition Language) command and is more efficient because it drops and re-creates the table.
At the same time, TRUNCATE is not transactional and cannot be rolled back in most cases. This is why it must be used with caution.
How to Truncate a Table in SQL
TRUNCATE is part of the SQL ANSI/ISO standard, so it is available in most databases with the following syntax:
1
TRUNCATE TABLE table_name;
Where table_name is the name of the table you want to empty.
In MySQL and PostgreSQL, the TABLE keyword is optional, so you can also write:
1
TRUNCATE table_name;
Special Notes
You cannot use TRUNCATE TABLE SQL queries on tables that:
Also, keep in mind that TRUNCATE TABLE:
How to Truncate Many Tables in SQL
In PostgreSQL, you can truncate multiple tables in one statement by separating them with commas using this syntax:
1
TRUNCATE TABLE table_name_1, table_name_2, [...], table_name_n;
For other databases, you need to issue separate TRUNCATE statements for each table:
1
TRUNCATE TABLE table_name_1;
2
-- ...
3
TRUNCATE TABLE table_name_n;
Empty a Table in a Visual Database Client
Emptying a table with a SQL TRUNCATE TABLE query is straightforward, but remembering the syntax and unique features across different databases can be challenging. To simplify this process, consider using a visual database client like DbVisualizer.
DbVisualizer lets you easily empty a table through an intuitive UI. These are the steps to get started:
- Right-click on the table you want to truncate (e.g., categories), then select the "Empty Table..." option.
Selecting the “Empty Table…” option in DbVisualizer
The following modal window will appear:
Here, you can choose whether to TRUNCATE or DELETE the table data. As shown in the "SQL Preview" section, DbVisualizer will automatically generate and execute the TRUNCATE query for you.
Wonderful! Emptying tables has never been easier.
Truncate a Table in SQL: Complete Example
Suppose you want to empty the users table with a TRUNCATE TABLE SQL query. First, you may want to verify that it contains some rows with the SQL COUNT() function:
1
SELECT COUNT(*)
2
FROM users;
Execute the query in a visual database client like DbVisualizer, and you will see that users contain 95 rows:
Then, launch the SQL TRUNCATE TABLE query:
1
TRUNCATE TABLE users;
The result will be:
Note that for performance reasons, the query does not return the number of deleted rows. The reason behind that is that InnoDB—the primary storage engine used by MySQL—does not store an internal row count, unlike MyISAM, which is now long obsolete.
Once our table is truncated, we can check that the users table is empty by executing the same COUNT() query we used before:
Et voilà! The row count is now 0, which means the users table is empty.
TRUNCATE TABLE SQL Best Practices
These are the recommended practices to follow when truncating a table in SQL:
Conclusion
In this guide, you explored the TRUNCATE TABLE SQL statement and learned how to remove data from one or more tables. You also saw how visual tools like DbVisualizer can simplify the process of emptying tables.
DbVisualizer is a powerful database client that streamlines tasks such as query execution, data exploration, and table management. It also offers advanced features like query optimization, SQL formatting, and schema visualization with ERD-like diagrams. Try DbVisualizer for free today!
FAQ
How does table truncation in sql works?
To be fast, table truncation in SQL works by removing all rows without logging individual row deletions. Speficailly, truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables. In detail, SQL Server deallocates the data pages used by the table, effectively freeing up storage in the quickest way possible. Unlike a DELETE statement, truncation does not fire triggers or return the number of rows affected.
What are some special considerations for the Transact-SQL TRUNCATE TABLE statement?
The SQL Server TRUNCATE TABLE statement is similar to the DELETE statement without a WHERE clause. However, TRUNCATE TABLE is faster and uses fewer system and transaction log resources. Additionally, the Transact-SQL TRUNCATE TABLE statement supports truncating specific partitions.
How to truncate table data in SQL Server?
To truncate table data in SQL Server, you must use a TRUNCATE statement with the following syntax:
1
TRUNCATE TABLE
2
{ database_name.schema_name.table_name | schema_name.table_name | table_name }
3
[ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }
4
[ , ...n ] ) ) ]
5
[ ; ]
What are some special considerations for the Oracle SQL TRUNCATE TABLE statement?
Some special considerations for the Oracle SQL TRUNCATE TABLE statement include:
What is the difference between DROP TABLE and TRUNCATE TABLE in SQL?
The key difference between DROP TABLE and TRUNCATE TABLE in SQL lies in their effects on the table and schema. DROP TABLE removes the entire table, including its structure, constraints, and data, permanently. It also frees the associated storage space. In contrast, TRUNCATE TABLE only removes the data within the table but retains the table structure. So, DROP removes the table while TRUNCATE only removes its data.
