When we’re looking to erase data from a table in SQL there are two typical options. We can use either the TRUNCATE statement or the DELETE statement. We may even use these inter-changeably without understanding the difference. At some point, someone is likely to ask at work or in an interview, “What is the difference between a truncate and delete in SQL?” – so let’s go over some of the key differences.
SQL syntax of each of these commands:
TRUNCATE TABLE <table_name>; — Typical truncate statement in SQL
DELETE FROM <table_name> WHERE <filter_column> = ‘value’; — Typical delete statement in SQL
At first glance, we notice that the delete command can include a where clause. If we truncate a table we can’t. On top of this, we can also include sub-queries and join conditions in our delete statement. Again, truncate can’t do this.
TRUNCATE Command
If we aren’t able to specify filters, joins, or sub-queries and using delete can also erase all records in a table then why use truncate? Because it’s faster. Actually, much faster. Using the truncate statement will take much less time to remove all records from a table than a delete because it doesn’t require the same logging methods or system resources.
Note: SQLite is a database that does not support TRUNCATE, but rather a modified DELETE statement that will truncate when appropriate.
Benefits of using TRUNCATE:
- Much faster than DELETE command
- Less resource contention
Downsides of using TRUNCATE:
- Simple delete with no filtering or joins allowed
- Unable to delete records in tables with foreign keys or indexed views (SQL Server)
- Limited logging
Using truncate will still maintain a table’s structural integrity. No columns or indexes are removed when using the command. Truncate can be viewed as the quickest bare-bones way to erase a table.
DELETE Command
While the truncate command will erase all records in a table, the delete command can do much more complex deletions. At a very basic level we can delete records from a table specifying a where clause filter.
But a delete can do much more than this. A delete can also join to other tables as well as provide subqueries in some cases.
Deletions are also resource intensive and can cause contention in a database so they should be used with caution. Unlike the truncate statement, a delete statement logs each record deletion and causes table row locks. This can prevent users and developers of the database from querying tables and going about their regular routines.
Benefits of using DELETE:
- Very flexible deletions without having to erase all records
- Record logging of deletions so they can be tracked – great for auditing
- Can remove records even when there are foreign keys on tables
Downsides of using DELETE:
- Takes longer than TRUNCATE
- Can cause table locks and prevent other users from normal database use
- Additional logging causes additional resource contention overall
Difference Between Truncate & Delete in SQL
We can see at its core that both operations can provide the same basic level deletion of a table. Beyond this we know that TRUNCATE will provide limited logging, but also erase a table much quicker. Using DELETE is much more versatile since we can delete very specific records within a table. However, a delete can cause additional resource contention.
Whichever one we use, we must be careful since erasing data in a database should always be proceeded with caution. Many times these functions will only be allowed for database admins or only specific developers. This isn’t surprising since we wouldn’t want this functionality in the wrong hands. It’s surprising how easily a command can be executed in a database and cause so much chaos.