{"id":539,"date":"2016-04-13T04:09:40","date_gmt":"2016-04-13T04:09:40","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=539"},"modified":"2025-02-08T00:57:58","modified_gmt":"2025-02-08T07:57:58","slug":"sql-truncate-table","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-truncate-table\/","title":{"rendered":"SQL TRUNCATE TABLE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL TRUNCATE TABLE<\/code> statement to remove all data in a table efficiently and fast.<\/p>\n\n\n\n

Introduction to the SQL TRUNCATE TABLE statement #<\/a><\/h2>\n\n\n\n

To delete all data from a table, you use the DELETE<\/a><\/code> statement without a WHERE<\/a><\/code> clause: <\/p>\n\n\n

DELETE FROM table_name;<\/code><\/span><\/pre>\n\n\n
However, the DELETE<\/code> statement is slow and not efficient for deleting all rows from a table with a lot of data. The reason is that the database system may need to acquire a lock and write a log for each individual row.<\/p>\n\n\n\n
To reduce locking and logging overhead, you can  use the TRUNCATE TABLE<\/code> statement. The TRUNCATE TABLE<\/code> statement deletes all data from a table very fast.<\/p>\n\n\n\n
Here’s the syntax of TRUNCATE TABLE<\/code> statement:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the table_name<\/code> that you want to delete data after the TRUNCATE TABLE<\/code> keyword.<\/p>\n\n\n\n
Some database systems such as MySQL and  PostgreSQL allow you to skip the TABLE<\/code> keyword so the TRUNCATE TABLE<\/code> statement is as simple as follows:<\/p>\n\n\n
TRUNCATE<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
When you issue the TRUNCATE TABLE<\/code> statement, the database system deletes all rows from the table by deallocating the data pages allocated by the table. By doing this, the database system can reduce the resources for logging and locking.<\/p>\n\n\n\n
To truncate multiple tables at once, you can specify a list of comma-separated table names in the TRUNCATE TABLE<\/code> clause:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> table_name1, table_name2, ...;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Not all database systems support this form of the TRUNCATE TABLE<\/code> statement. If you are using the one that does not, you must issue multiple TRUNCATE TABLE<\/code> statements to truncate multiple tables.<\/p>\n\n\n\n
SQL TRUNCATE TABLE statement example #<\/a><\/h2>\n\n\n\n
First, create a new table<\/a> called items<\/code> :<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> items (\n  id<\/span> INT<\/span> GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, execute the following INSERT<\/code> statement as many times as you want to insert<\/a> data into the items<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  items (id<\/span>)\nVALUES<\/span>\n  (DEFAULT<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that if you use a database system that supports user-defined functions or stored procedures, you can put this statement inside a loop. <\/p>\n\n\n\n
For example, the following procedure in PostgreSQL loads data into the items<\/code> table with the number of rows specified by the n<\/code> parameter.<\/p>\n\n\n
CREATE<\/span> PROCEDURE<\/span> load_data (n INT<\/span>) AS<\/span> $$\nBEGIN<\/span>\n  FOR<\/span> i IN<\/span> 1.<\/span>.n LOOP<\/span>\n    INSERT<\/span> INTO<\/span> items(id<\/span>) VALUES<\/span>(DEFAULT<\/span>);\n  END<\/span> LOOP<\/span>;\nEND<\/span>;  \n$$ LANGUAGE plpgsql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement calls the load_data<\/code> procedure to insert 10,000 rows into the items<\/code> table.<\/p>\n\n\n
CALL<\/span> load_data(10000<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, use the TRUNCATE TABLE<\/code> statement to truncate data from the items<\/code> table:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> items;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL TRUNCATE TABLE vs. DELETE Statement #<\/a><\/h2>\n\n\n\n
The following table compares the SQL DELETE<\/code> and TRUNCATE TABLE<\/code> statements:<\/p>\n\n\n\n
Aspect<\/th>DELETE<\/th>TRUNCATE TABLE<\/th><\/tr><\/thead>
Purpose<\/strong><\/td>Deletes one or some rows from a table based on a WHERE<\/code> clause. Without a WHERE<\/code> clause, the DELETE<\/code> statement also delete all rows from a table.<\/td>Deletes all rows from a table.<\/td><\/tr>
Conditional Removal<\/strong><\/td>Allows to specify a condition for specifying rows to delete in the WHERE<\/code> clause.<\/td>Does not support conditions.<\/td><\/tr>
Transaction Support<\/strong><\/td>Logs the delete operations and and supports rollbacks as part of a transaction.<\/td>Provides very minimal logging. Some database system also supports rollbacks in transactions.<\/td><\/tr>
Performance<\/strong><\/td>Slower<\/td>Faster <\/td><\/tr>
Triggers<\/strong><\/td>Activates DELETE<\/code> triggers.<\/td>Does not activate DELETE<\/code> triggers. Some database systems support TRUNCATE triggers.<\/td><\/tr>
Identity Column Reset<\/strong><\/td>Does not reset the value of identity columns (auto-increment).<\/td>Resets the value of identity columns to their seed value.<\/td><\/tr>
Foreign Key Constraints<\/strong><\/td>Follows foreign key constraints.<\/td>Cannot truncate a table referenced by a foreign key. Some database systems support CASCADE<\/code> option to delete foreign key constraints before truncating table<\/td><\/tr>
Usage<\/strong><\/td>When you want to delete specific rows based on a condition or need to use DELETE triggers<\/td>When you need to quickly delete all from a table fast.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n