SQL TRUNCATE TABLE Statement

This page explains SQL TRUNCATE statement, what it does and how it differs from deleting all rows in a table.

[toc]

How to TRUNCATE a table

Use the keywords TRUNCATE TABLE followed by table name.

For example, this query truncates the table named historical_prices:

TRUNCATE TABLE historical_prices;

TRUNCATE in different DMBS

Note that TRUNCATE is not part of standard SQL. However, it is available in many DBMS popular in finance, such as MySQL, PostgreSQL, SQL Server, and Oracle. The information on this page applies to all of these, although there may be additional optional parameters available in some of them.

In some DMBS, including MySQL and PostgreSQL, you can omit the TABLE keyword, so this also works:

TRUNCATE historical_prices;

AUTO_INCREMENT reset

Besides deleting all rows in the table, the TRUNCATE statement also resets AUTO_INCREMENT to its starting value (typically 1).

It is different in PostgreSQL, where AUTO_INCREMENT is not reset by default. If you do wish to reset it, add the keywords RESTART IDENTITY after the table name:

TRUNCATE TABLE historical_prices RESTART IDENTITY;

TRUNCATE vs. DELETE all rows

Truncating is more than just deleting all data in a table (DELETE without WHERE). Effectively it means deleting the entire table and recreating it again as a new empty table with the same name and schema.

TRUNCATE is a DDL kind of operation (Data Definition Language - changing table schema and data definition), while DELETE is a DML kind (Data Manipulation Language - changing the data, but not touching the schema).

If the table is complex with a lot of rows, truncating is faster than deleting all rows.

TRUNCATE vs. DROP TABLE

TRUNCATE clears the data (effectively drops the table and recreates it empty), while DROP TABLE deletes the entire table and does not recreate it.

Privileges needed for TRUNCATE

Because truncating a table is a different operation then merely deleting rows, it requires user privileges other than DELETE. The particular privileges are DBMS-specific. To run TRUNCATE TABLE you need:

  • TRUNCATE privilege in PostgreSQL
  • DROP privilege in MySQL (there is no TRUNCATE privilege)
  • ALTER privilege in SQL Server
  • DROP ANY TABLE privilege in Oracle