intro
Many developers are aware that truncating datasets is often a faster alternative to deleting rows using WHERE or other clauses. Explore the reason behind this database behavior here!
Data in your database is far from a miracle. Many applications are based upon it, and all developers know that no data set in a database is a permanent duct tape to all of your problems.
In the software world, data has to be selected, updated, and sometimes deleted. Deleting data, however, may not be a piece of cake and as there are multiple use cases necessitating such an action to be taken, there are multiple ways of going around and solving such problems, too. Truncating data is one of them.
Learn more about the TRUNCATE SQL statement!
What Is Data Truncation?
Let us start from the bottom — what constitutes data truncation in the first place?
Data truncation refers to all of the rows existing in a table being deleted at once. If we update or select all of the rows in a table instead, that wouldn’t constitute as truncation because to truncate a table we have to get rid of data instead of acting on it — that’s all a truncate SQL statement is.
A truncate SQL statement looks like this regardless of the database management system being in use:
1
TRUNCATE [table_name];
In other words, to remove all of the existing rows in a table, we truncate it and to truncate a table, we employ the TRUNCATE SQL clause in conjunction with the name of our table.
The TRUNCATE SQL clause works the same in all relational database management systems, and a successful truncate SQL operation would look like this:
Bear in mind that when executed in relational database management systems, the truncate SQL statement is likely to return 0 rows saying “OK. No rows were affected” even if there are some rows that have been removed. The products table has 9 rows:
However, once we execute a TRUNCATE SQL query in DbVisualizer, we see the message “No rows were affected” as shown below:
That’s not the case and rows were affected (the products table had 9 entries while now it has 0):
Messages can sometimes be tricky to interpret, right? Our table now having 0 rows directly results from a successful TRUNCATE operation.
The Internals of the TRUNCATE SQL Statement
The truncate SQL statement is universal across all database management systems, and it cannot be used in conjunction with any other SQL queries or clauses. That’s because it has one task to perform: all it does, and all it should do, is remove all data from the table specified by the user (you.) Nothing else.
The SQL truncate statement has one task to perform — and it’s the one task that this statement executes well.
TRUNCATE will always be faster than ordinary DELETE queries and that’s because of one simple reason — while all DELETE SQL queries have to scan through all of the rows before deleting them, TRUNCATE doesn’t do that. TRUNCATE statement doesn’t modify any rows, it doesn’t count them, and doesn’t even generate any transaction logs.
It is designed to be blazing fast when dealing with any use case and that’s the primary reason behind the “if you’re deleting all of the rows, switch DELETE queries to TRUNCATE instead.” You should do that because that way, you will allow your database to avoid the overhead that comes with DELETE statements.
When to Use TRUNCATE
With that said, many of you are probably searching for the answer to the question “when should I truncate my data?”
The answer to that question is also quite straightforward and easy: execute the truncate SQL statement whenever you have to delete all rows within a table. This begs for the answer to another question: what if you have multiple tables to truncate? And the answer to this question is also quite easy to understand and straightforward if you think about it — truncate multiple tables by executing a loop through them.
Such a snippet will do just fine (PHP-based example):
1
<?php
2
$your_database_name = 'demodb'; // the name of your database here
3
$tables = array(); // an empty array to determine the names of the tables we need to run the statement on
4
5
$query = $db->prepare
6
("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = :dbname");
7
8
$query->execute(array(
9
":dbname" => $your_database_name
10
));
11
12
foreach($query_results as $query_result) {
13
$table_name = $query_result['TABLE_NAME'];
14
array_push($tables, $table_name); // pushing (adding) elements to the end of the array
15
}
16
17
// ...
18
19
foreach($tables as $table) {
20
// printing the TRUNCATE TABLE statements (one per line)
21
echo "TRUNCATE TABLE $table\r\n";
22
}
23
?>
Now you know how to obtain a set of tables to truncate data from, too. Woohoo!
SQL Clients and Query Optimization
The TRUNCATE SQL statement is not the holy grail. It’s not the holy grail because its use case is extremely narrow: all it can do is delete all rows from one table and if you use a loop, multiple tables. This means that this statement is useful, but it won’t be the only statement you’re going to be working with as a developer or DBA: you need to take care of the performance of other statements — SELECT, UPDATE, INSERT, and yes, DELETE too.
Taking care of them is relatively simple — keep your configuration file fresh (optimizing InnoDB through my.cnf, anyone?), don’t store unnecessary data in your database (normalize your tables), and perform other optimization techniques such as only selecting necessary rows when returning a result set.
There are certain SQL clauses like the LIMIT clause that will be very useful for those needing quick results — the less data your database returns or acts on, the faster your queries will complete. DBAs know that this is only the tip of the iceberg — your database will act up regardless, and if you’re not a DBA, you’ve got to do something about that.
Thankfully, DbVisualizer is here to save the day — with its extensive features you can always be sure that your database (and the data you sometimes need to truncate within) is in good hands.
DbVisualizer supports more than 50 databases so you’re never losing when you choose the tool to assist your use case, and its extensive features will make sure that your database doesn’t fall short on any front. From observing the databases within your database instance and providing views specific to DBAs:
To powerful query building capabilities (for those wondering, drag & drop is also a feature provided by design meaning that you can drag & drop tables from databases on the left side to the query builder and tell DbVisualizer how best to build your queries):
Crafting SQL queries with a SQL editor beside you. And that’s only the beginning — with frequent updates to the tool, you can be sure that DbVisualizer will help you squash any database problem you will face now or in the future.
Grab a free 21-day trial of DbVisualizer today, stay safe (and educated), and until next time.
FAQ
What does the TRUNCATE SQL statement do?
The TRUNCATE SQL statement deletes all data in a specific table. This statement is not applicable to databases (executing a TRUNCATE DATABASE [name]statement is impossible), however, it can be combined with a loop to truncate multiple tables if/when necessary.
Why is TRUNCATE faster than a DELETE?
An SQL TRUNCATE query will always be faster than a DELETE query because:
Is the TRUNCATE statement a substitute for a DELETE?
No — the TRUNCATE SQL statement is not a substitute for a DELETE and it will never become one. DELETE and TRUNCATE statements have very different tasks to perform (DELETE deletes a set of rows from a table and accepts clauses after itself, TRUNCATE deletes all rows within a table and does not), however, they can be used in conjunction, within loops, or different SQL statements.
Where can I learn more about databases?
You can learn more about database availability, security, performance, and related topics by following blogs like TheTable, attending industry conferences, seminars and workshops, and reading books.
