SQL TRUNCATE TABLE Statement: The Complete Guide

Jan 3, 2020 · 5 min read · mysql oracle postgresql sql-server ·

The SQL TRUNCATE TABLE statement is a useful statement for removing data in SQL. I'll explain what the statement does and show you some examples in this article.

What Does the SQL TRUNCATE TABLE Statement Do?

The SQL TRUNCATE statement, or TRUNCATE TABLE statement, removes all data from a table.

It's similar to the DELETE statement without a WHERE clause.

TRUNCATE TABLE Syntax

The syntax for the SQL TRUNCATE statement is slightly different in each database.

The basic TRUNCATE statement looks the same:

1TRUNCATE TABLE [schema_name.]table_name

schema_name: This is optional. If your table belongs to a different schema, you specify it here.
table_name: The name of the table to remove the data from.

However, there are several options unique to each database.

Oracle

1TRUNCATE TABLE [schema_name.]table_name
2[PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG]
3[DROP STORAGE | REUSE STORAGE];

SQL Server

1TRUNCATE TABLE [schema_name.]table_name
2[ WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) ) ];

MySQL

1TRUNCATE TABLE [schema_name.]table_name;

Postgres

1TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
2[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ];

So what do all of these parameters mean?

Oracle:

PRESERVE MATERIALIZED VIEW LOG: Optional, and default. If you specify this, then the materialized view log will be preserved when you truncate the table.
PURGE MATERIALIZED VIEW LOG: Optional. If you specify this, then the materialized view log will be purged when you truncate the table.
DROP STORAGE: Optional, and default. If you specify this, all storage that is allocated to the truncated rows will be deallocated (except the space allocated by MINEXTENTS).
REUSE STORAGE: If you specify this, all storage that is allocated to the truncated rows will remain allocated to the table.

SQL Server:

WITH PARTITIONS: Specifies the partitions to truncate or from which all rows are removed.

PostgreSQL:

RESTART IDENTITY: Automatically restarts the sequences owned by columns of this table.
CONTINUE IDENTITY: Does not change the values of sequences. This is the default.
CASCADE: Automatically truncate all tables that have foreign key references to this table.
RESTRICT: Does not truncate any tables that have foreign key references to this table. This is the default.

What's the Difference Between TRUNCATE and DELETE?

So what's the difference between the TRUNCATE and DELETE statements?

DELETE can delete specific records, TRUNCATE deletes all records

You may have noticed that there is no WHERE clause on the TRUNCATE statement. This is because all of the data in the table is removed when you run a TRUNCATE statement.

With the DELETE statement, you can delete all records, or use the WHERE clause to delete some records.

DELETE Allows You To Rollback, TRUNCATE Does Not Allow Rollback

With the DELETE statement, you can run a ROLLBACK statement and effectively restore the data you have deleted. However, if you run the ROLLBACK statement after a TRUNCATE statement, then you won't be able to recover the data. You can't rollback a TRUNCATE statement.

DELETE generates an undo log, TRUNCATE does not

This is what makes TRUNCATE statements faster than DELETE statements. When you run a DELETE statement, the undo log is generated so that the ROLLBACK statement can be run.

DELETE takes locks on rows, TRUNCATE does not

When you run a DELETE statement, the rows are locked while they are deleted. With the TRUNCATE statement, they are not locked.

DELETE causes delete triggers to fire, TRUNCATE does not

If you use triggers, this might cause different behaviour in your database.

DELETE is a DML command, TRUNCATE is a DDL command.

This difference means that DELETE can be rolled back and TRUNCATE cannot.

SQL TRUNCATE TABLE Examples

Let's take a look at some examples of the TRUNCATE TABLE statement.

Example 1 - Basic Truncate

This is a basic TRUNCATE statement.

1TRUNCATE TABLE student;

Example 2 - Materalized View Log (Oracle)

This example uses the PRESERVE MATERIALIZED VIEW LOG keyword. Related: SQL Views: The Complete Guide.

1TRUNCATE TABLE student
2PRESERVE MATERIALIZED VIEW LOG;

Example 3 - Storage (Oracle)

This example uses the REUSE STORAGE command.

1TRUNCATE TABLE student
2REUSE STORAGE;

Example 4 - All Parameters (Oracle)

This example uses both the "materialized view log" parameter and the "storage" parameter.

1TRUNCATE TABLE student
2PRESERVE MATERIALIZED VIEW LOG
3DROP STORAGE;

Example 5 - Restart Identity (PostgreSQL)

This example uses the Restart Identity parameter in PostgreSQL to restart the sequences related to this table.

1TRUNCATE TABLE student
2RESTART IDENTITY;

As you can see, the TRUNCATE TABLE statement is pretty simple to use. In most cases, you can just run the "TRUNCATE TABLE tablename" command.

Truncate Table in a Stored Procedure

Are you trying to run a TRUNCATE TABLE statement inside a stored procedure? And are you getting an error?

Your procedure may look like this:

1CREATE PROCEDURE testProc IS
2BEGIN
3  TRUNCATE TABLE tablename;
4END testProc;

If you run this, you might get an error.

This error happens because the stored procedure is running the DDL statement, and some instances of the DDL could invalidate the stored procedure.

To resolve it, you could run the command as Execute Immediate.

Change your stored procedure to call the TRUNCATE statement like this:

1CREATE PROCEDURE testProc IS
2BEGIN
3  EXECUTE IMMEDIATE 'TRUNCATE TABLE tablename';
4END testProc;

This procedure should now run successfully.

Conclusion

So, that's how you use the TRUNCATE statement, and how it's different to the DELETE statement.

Lastly, if you enjoy the information and career advice I've been providing, sign up to my newsletter below to stay up-to-date on my articles. You'll also receive a fantastic bonus. Thanks!