Skip to main content

TRUNCATE TABLE (SQL)

Removes all data from a table and resets counters.

Synopsis

TRUNCATE TABLE [restriction] tablename

Description

The TRUNCATE TABLE command removes all rows from a table and resets all table counters.

TRUNCATE TABLE resets the internal counters used for generating RowID field, IDENTITY field, and SERIAL (%Library.Counter) field sequential integer values. InterSystems IRIS assigns a value of 1 for these fields in the first row inserted into a table following a TRUNCATE TABLE. Performing a DELETE on all rows of a table does not reset these internal counters.

TRUNCATE TABLE resets the internal counter used for generating stream field OID values when data is inserted into a stream field. Performing a DELETE on all rows of a table does not reset this internal counter.

TRUNCATE TABLE always sets the %ROWCOUNT local variable to -1; it does not set %ROWCOUNT to the number of rows deleted.

TRUNCATE TABLE does not reset the ROWVERSION counter.

TRUNCATE TABLE suppresses the pulling of base table triggers that are otherwise pulled during DELETE processing. Because TRUNCATE TABLE performs a delete with %NOTRIGGER behavior, the user must have been granted the %NOTRIGGER privilege (using the GRANT statement) in order to run TRUNCATE TABLE. This aspect of TRUNCATE TABLE is functionally identical to:

DELETE %NOTRIGGER FROM tablename
Note:

The DELETE command can also be used to delete all rows from a table. DELETE provides more functionality than TRUNCATE TABLE, including returning the number of rows deleted in %ROWCOUNT. DELETE does not reset internal counters.

TRUNCATE TABLE provides compatibility for code migration from other database software.

To truncate a table:

  • The table must exist in the current (or specified) namespace.

    InterSystems IRIS issues an SQLCODE -30 error when the name of a view is specified as the tablename argument, a subquery is specified as the tablename argument, or is the specified table cannot be located.

  • The user must have the %NOTRIGGER administrative privilege, even if no triggers are defined. Failing to have this privilege results in an SQLCODE –99 error with the %msg User does not have %NOTRIGGER privileges.

  • The user must have DELETE privilege for the table. Failing to have this privilege results in an SQLCODE -99 with the %msg User 'name' is not privileged for the operation. You can determine if the current user has DELETE privilege by invoking the %CHECKPRIV command. You can determine if a specified user has DELETE privilege by invoking the $SYSTEM.SQL.Security.CheckPrivilege()Opens in a new tab method. For privilege assignment, refer to the GRANT command.

  • The table cannot be defined as READONLY. Attempting to compile a TRUNCATE TABLE that references a read-only table results in an SQLCODE -115 error. Note that this error is now issued at compile time, rather than only occurring at execution time. See the description of READONLY objects in Other Options for Persistent Classes.

  • All of the rows must be available for deletion. By default, if one or more rows cannot be deleted, the TRUNCATE TABLE operation fails and no rows are deleted.

    TRUNCATE TABLE fails if the table is locked by another process in either EXCLUSIVE MODE or SHARE MODE. Attempting a TRUNCATE TABLE operation on a locked table results in an SQLCODE -110 error, with a %msg such as the following: Unable to acquire lock for DELETE of table 'Sample.MyStuff' on row with RowID = '3' (where the specified RowID is the first row in the table).

    TRUNCATE TABLE fails if deleting a row would violate foreign key referential integrity. No rows are deleted and TRUNCATE TABLE issues an SQLCODE -124 error. This default behavior is modifiable, as described below.

Atomicity

TRUNCATE TABLE does not occur within an automatically initiated transaction, and therefore no journaling or rollback option is provided.

If journaling and the option to rollback TRUNCATE TABLE is required, you must explicitly specify a START TRANSACTION and conclude with an explicit COMMIT or ROLLBACK.

This is the same as SET TRANSACTION %COMMITMODE= NONE or 0 (no auto transaction) — No transaction is initiated when you invoke TRUNCATE TABLE. A failed TRUNCATE TABLE operation can leave the database in an inconsistent state, with some rows deleted and some not deleted. To provide transaction support in this mode you must use START TRANSACTION to initiate the transaction and COMMIT or ROLLBACK to end the transaction.

TRUNCATE TABLE for a sharded table is always performed using SET TRANSACTION %COMMITMODE NONE, even when the user has explicitly set SET TRANSACTION %COMMITMODE EXPLICIT.

Restriction Arguments

To use a restriction argument, you must have the corresponding admin-privilege for the current namespace. Refer to GRANT for further details.

Specifying restriction argument(s) restricts processing as follows:

  • %NOCHECK — suppress referential integrity checking for foreign keys that reference the rows being deleted.

  • %NOLOCK — suppress row locking of the rows being deleted. This should only be used when a single user/process is updating the database.

  • %NOJOURN — suppress journaling of the rows being deleted and disable transactions for the duration of the deletions. None of the changes made in any of the rows are journaled, including any triggers fired. If you perform a ROLLBACK after a statement with %NOJOURN, the changes made by the statement will not be rolled back.

You can specify multiple restriction arguments in any order. Multiple arguments are separated by spaces.

If you specify a restriction argument when deleting a parent record, the same restriction argument will be applied when deleting the corresponding child records.

TRUNCATE TABLE always performs a delete with implicit %NOTRIGGER behavior, and requires the corresponding admin-privilege.

Referential Integrity

InterSystems IRIS uses the system-wide configuration setting to determine whether to perform foreign key referential integrity checking; the default is to perform foreign key referential integrity checking. You can set this default system-wide, as described in Foreign Key Referential Integrity Checking. To determine the current system-wide setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab.

During a TRUNCATE TABLE operation, for every foreign key reference, a shared lock is acquired on the corresponding row in the referenced table. This row is locked until the end of the transaction. This ensures that the referenced row is not changed before a potential rollback of the TRUNCATE TABLE.

Transaction Locking

InterSystems IRIS performs standard locking on a TRUNCATE TABLE operation. Unique field values are locked for the duration of the current transaction.

The default lock threshold is 1000 locks per table, which means if you delete more than 1000 unique field values from a table during a transaction, the lock threshold is reached and InterSystems IRIS automatically elevates the locking level from unique field value locks to a table lock. This permits large-scale deletes during a transaction without overflowing the lock table.

You can determine the current system-wide lock threshold value using the $SYSTEM.SQL.Util.GetOption("LockThreshold")Opens in a new tab method. This system-wide lock threshold value is configurable:

You must have USE permission on the %Admin Manage Resource to change the lock threshold. InterSystems IRIS immediately applies any change made to the lock threshold value to all current processes.

For further details on transaction locking refer to Transaction Processing.

Imported SQL Code

The ImportDDL("IRIS")Opens in a new tab and Run()Opens in a new tab methods do not support the TRUNCATE TABLE command. A TRUNCATE TABLE command found in an SQL code file imported by these methods is ignored. These import methods do support the DELETE command.

Arguments

restriction

An optional argument specifying one or more of the following restriction keywords, separated by spaces: %NOCHECK, %NOLOCK.

tablename

The table from which you are deleting all rows. A table name can be qualified (schema.table), or unqualified (table). An unqualified name is matched to its schema using either a schema search path (if provided) or the default schema name.

Examples

The following two Dynamic SQL examples compare DELETE and TRUNCATE TABLE. Each example creates a table, inserts rows into the table, deletes all the rows in the table, then inserts a single row into the now empty table.

The first example uses DELETE to delete all the records in the table. Note that DELETE does not reset the RowID counter:

  SET tcreate = "CREATE TABLE SQLUser.MyStudents (StudentName VARCHAR(32),StudentDOB DATE)"
  SET tinsert = "INSERT INTO SQLUser.MyStudents (StudentName,StudentDOB) "_
                "SELECT Name,DOB FROM Sample.Person WHERE Age <= '21'"
  SET tinsert1 = "INSERT INTO SQLUser.MyStudents (StudentName,StudentDOB) VALUES ('Bob Jones',60123)"
  SET tdelete = "DELETE SQLUser.MyStudents"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(tcreate)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
   WRITE rset.%StatementTypeName,!

   NEW %ROWCOUNT,%ROWID
   SET qStatus = tStatement.%Prepare(tinsert)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
   WRITE rset.%StatementTypeName," rowcount ",rset.%ROWCOUNT,!

   SET qStatus = tStatement.%Prepare(tdelete)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
   WRITE rset.%StatementTypeName," rowcount ",rset.%ROWCOUNT,!

  SET qStatus = tStatement.%Prepare(tinsert1)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
   WRITE rset.%StatementTypeName," rowcount ",rset.%ROWCOUNT," RowID ",rset.%ROWID,!
  &sql(DROP TABLE SQLUser.MyStudents)

The second example uses TRUNCATE TABLE to delete all the records in the table. Note that %StatementTypeNameOpens in a new tab returns “DELETE” for TRUNCATE TABLE. Note that TRUNCATE TABLE does reset the RowID counter:

  SET tcreate = "CREATE TABLE SQLUser.MyStudents (StudentName VARCHAR(32),StudentDOB DATE)"
  SET tinsert = "INSERT INTO SQLUser.MyStudents (StudentName,StudentDOB) "_
                "SELECT Name,DOB FROM Sample.Person WHERE Age <= '21'"
  SET tinsert1 = "INSERT INTO SQLUser.MyStudents (StudentName,StudentDOB) VALUES ('Bob Jones',60123)"
  SET ttrunc = "TRUNCATE TABLE SQLUser.MyStudents"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(tcreate)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
   WRITE rset.%StatementTypeName,!

  NEW %ROWCOUNT,%ROWID
  SET qStatus = tStatement.%Prepare(tinsert)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
   WRITE rset.%StatementTypeName," rowcount ",rset.%ROWCOUNT,!

  SET qStatus = tStatement.%Prepare(ttrunc)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
   WRITE rset.%StatementTypeName," (TRUNCATE TABLE) rowcount ",rset.%ROWCOUNT,!

  SET qStatus = tStatement.%Prepare(tinsert1)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
   WRITE rset.%StatementTypeName," rowcount ",rset.%ROWCOUNT," RowID ",rset.%ROWID,!
  &sql(DROP TABLE SQLUser.MyStudents)

See Also

FeedbackOpens in a new tab