SQL Standard

Truncate vs. Delete SQL

There are two main methods for removing data in a SQL database: truncate and delete. Although the process may seem closely related, understanding the difference between these commands can be beneficial in determining how to handle data.

Let us break down the difference between the truncate and delete commands in SQL.

SQL Truncate Command

The truncate command in SQL is known as a data definition language. The truncate commands remove the data from a table while preserving the table structure. Unlike the delete command, you cannot filter for specific records using the WHERE clause.

Similarly, we cannot perform rollback after running the truncated commands, since no logs are preserved for this command.

NOTE: Some database engines support rollback for truncate commands, but this is not universal.

If a table is part of an indexed view or referenced by a foreign key, the truncate command will not work on that table.

The command syntax is as shown:

TRUNCATE TABLE TABLE_NAME;

The truncate command is faster than delete as it does not perform a scan for the records before removal. It also ensures that the entire table is locked before removing the data, thus reducing transaction space.

Depending on the database engine, you need explicit permissions to perform a truncate operation on a table. For example, in SQL Server, you need the ALTER table permission. MySQL requires DROP permission, PostgreSQL requires TRUNCATE permission, and Oracle requires the DROP ANY TABLE permissions.

SQL Delete Command

The delete command is a data manipulation command. It allows you to remove records from a database table and returns the number of deleted records. The delete commands remove the rows from a table and not the entire table from the database.

Unlike the truncate command, the delete command allows you to filter the target records using the WHERE clause.

The command syntax is shown below:

DELETE FROM TABLE_NAME;

To filter for specific records, you can use the syntax as shown below:

DELETE FROM TABLE_NAME WHERE condition;

NOTE: If you do not specify the target columns, the command will remove all the records from the given table.

Unlike the truncate command, which locks the entire table, the delete command locks individual rows leading to a higher transaction space, especially on a large table.

To run a delete command on a table, you need the DELETE permission.

Summary Table

The following table summarizes the differences between the SQL delete and truncate commands.

  SQL TRUNCATE SQL DELETE
Type Data Definition Language Data Manipulation Language
Lock-Type Table Row
Rollback Some database engines Yes
Transaction Logs Entire table Per row
Transaction space usage Less Higher
Trigger support No Yes
Speed Very fast Slow
Removal type All rows All or specific rows (as specified)
Works with an indexed view? No Yes
Identity column reset Some database Engines No
Returns removed records? No Yes

In most cases, choosing the best operation type will depend on your specific needs. However, consider the following points:

  1. For specific rows, opt for delete.
  2. Truncate is faster than delete; hence, use truncate if you want to remove all rows in a table.
  3. If you do not care about the table structure, use other commands, such as drop.

Conclusion

This article discusses two fundamental commands to remove data in an SQL database. Using this guide, you can explicitly specify what the truncate command or the delete command does in a relational database. We hope you found this article helpful. Check the other Linux Hint articles for more tips and tutorials.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list