SQL TRUNCATE Table: Tutorial & Explanation

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

This lesson will cover the SQL TRUNCATE statement, providing a brief tutorial of the command and some code examples from various database management systems.

SQL TRUNCATE

SQL TRUNCATE is used to delete all rows from a table. The TRUNCATE statement is a version of DELETE that does not use a WHERE clause. It can be better than dropping and re-adding the table through SQL. The TRUNCATE basically performs a drop and re-add of the table, which is faster than removing the data row-by-row.

The command cannot be rolled back (there is no undo!); it will not work if another table is locked (in use by another person or program), or if there are other tables that reference the current table.

Difference between DELETE and TRUNCATE

The DELETE statement will only delete rows based on a given condition in an SQL WHERE clause (or all rows if you omit the WHERE), but it doesn't free up any of the space that was left by the table. This leads to bloat and potentially HUGE files.

Another important feature of TRUNCATE is that it will reset auto-numbered/sequential value fields. For example, if you have a primary key that grows incrementally by 1 each time a record is created, the TRUNCATE function will reset everything back; the first new record entered into the table will be a 1.

Difference between DROP and TRUNCATE

The DROP command removes the table from the database; it is gone forever and cannot be brought back. TRUNCATE, on the other hand, wipes out all the rows but keeps the table and all of its settings. This means that indexes, access privileges, integrity constraints (e.g., Employee IDs must all be numbers), and relationships to other tables are preserved.

Not only does TRUNCATE delete all the rows in the table, it frees up the space for other uses. Some databases, however let the administrator decide whether to keep this space or free it up. (See the Oracle example in this lesson.)

SQL TRUNCATE in action

The figure table before truncate shows a small sample table before the TRUNCATE command is carried out. Note the column ID: it is auto-numbered from 1 to 4.

table before truncate
table before truncate

After the TRUNCATE command, the table will resemble the figure table after truncate: The auto-numbering restarted at 1.

table after truncate
table after truncate

The following examples show how to use TRUNCATE in the major database management systems: Oracle, SQL Server, and MySQL. Each code sample removes all data from the Expired_Credentials table in the Employees database.

Oracle

The following statement deletes all rows from the table and releases the space back to the database.

TRUNCATE TABLE expired_credentials

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support