TRUNCATE TABLE Statement

 

SQL: TRUNCATE TABLE Statement

This SQL tutorial explains how to use the SQL TRUNCATE TABLE statement with syntax and examples.

Description

The SQL TRUNCATE TABLE statement is used to remove all records from a table. It performs the same function as a DELETE statement without a WHERE clause.

Warning: If you truncate a table, the TRUNCATE TABLE statement can not be rolled back in some databases.

Syntax

The syntax for the TRUNCATE TABLE statement in SQL is:

TRUNCATE TABLE table_name;

Parameters or Arguments

table_name

The table that you wish to truncate.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example

You might choose to truncate a table instead of dropping the table and recreating it. Truncating a table can be faster and does not affect any of the table's indexes, triggers, and dependencies. It is also a fast way to clear out the records from a table if you don't need to worry about rolling back.

Let's look at an example of how to use the TRUNCATE TABLE statement in SQL.

In this example, we have a table called suppliers with the following data:

supplier_id	supplier_name	city	state
100	Microsoft	Redmond	Washington
200	Google	Mountain View	California
300	Oracle	Redwood City	California
400	Kimberly-Clark	Irving	Texas
500	Tyson Foods	Springdale	Arkansas
600	SC Johnson	Racine	Wisconsin
700	Dole Food Company	Westlake Village	California
800	Flowers Foods	Thomasville	Georgia
900	Electronic Arts	Redwood City	California

Enter the following TRUNCATE TABLE statement:

TRUNCATE TABLE suppliers;

Then select the data from the suppliers table again:

SELECT * FROM suppliers;

These are the results that you should see:

supplier_id	supplier_name	city	state

This example would truncate the table called suppliers and remove all records from that table. It would be equivalent to the following DELETE statement in SQL:

DELETE FROM suppliers;

Both of these statements would result in all data from the suppliers table being deleted. The main difference between the two is that you can roll back the DELETE statement if you choose, but you may not be able to roll back the TRUNCATE TABLE statement in all SQL databases.

If you need to prefix the table name with the database name, you can rewrite the TRUNCATE TABLE statement as follows:

TRUNCATE TABLE scott.suppliers;

This example would truncate the table called suppliers in the database called scott.