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:
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:
To filter for specific records, you can use the syntax as shown below:
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:
- For specific rows, opt for delete.
- Truncate is faster than delete; hence, use truncate if you want to remove all rows in a table.
- 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.
