Let us discuss how to use the TRUNCATE command in standard SQL.
Command Syntax
The syntax for the TRUNCATE command is as shown below:
The command takes the name of the table you wish to delete. Keep in mind that this command does not support views, models, or external tables.
The truncate command is most useful when you need to remove all the data within a table without deleting the table schema.
Unlike its counterpart (DELETE), the truncate command does not support filtering for specific records using the WHERE clause.
Although performing rollbacks for the TRUNCATE command is supported by some database engines, such as SQL Server, this is not a supported feature in standard SQL.
Other features of the truncate command include:
- The command fails if the table is referenced by a foreign key or contains an indexed view.
- It is relatively faster than the delete command.
- It requires less transactional space as it locks an entire table instead of individual records.
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 database requires the DROP ANY TABLE permissions.
SQL Truncate Examples
Let us illustrate how to use the SQL truncate command. Assume we have a sample table known as customers with the records as shown below:
We can truncate the table as shown below:
The previous command should remove all the data in the table. Querying the table should return an empty row set.
If a foreign key references the table, the truncate command will fail. An example output is provided below:
Conclusion
In this tutorial, we discussed how the truncate command works and how to use the truncate command to remove data from a table. We hope you found this article helpful. Check the other Linux Hint articles for more tips and tutorials.
