Removing rows from a table using the TRUNCATE statement

To remove all the rows from a table, use the TRUNCATE statement.

The TRUNCATE statement looks like this:

   TRUNCATE TABLE table-name

This is equivalent to the following DELETE statement:

   DELETE FROM table-name

The TRUNCATE statement has some additional options that are not available on the DELETE statement to control how triggers are handled during the truncation operation and the behavior of the table's identity column after truncation is complete.

The default for TRUNCATE is to not activate any delete triggers during truncation. If you want delete triggers to be activated, you must use the DELETE statement.

For an identity column, you can specify to either continue generating identity values in the same way as if the truncate operation did not occur, or you can request to have the identity column start from its initial value when it was first defined. The default is to continue generating values.

Note: The TRUNCATE statement does not return the number of rows deleted in SQLERRD(3) of the SQLCA or the ROW_COUNT diagnostics item in the GET DIAGNOSTICS statement.