Previous section   Next section

TRUNCATE TABLE

Removes all data from a table and resets counters.

Synopsis

TRUNCATE TABLE [restriction] tablename

Arguments

restriction Optional — One or more of the following restriction keywords, separated by spaces: %NOCHECK, %NOLOCK.
tablename The table from which you are deleting all rows. You can also specify an updateable view through which you can delete all of the rows of a table. A table name (or view 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 system-wide default schema name.

Description

The TRUNCATE TABLE command removes all rows from a table, and resets all table counters. You can truncate a table directly, or through a view. Truncating a table through a view is subject to delete requirements and restrictions, as described in CREATE VIEW.
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. If the specified table cannot be located, InterSystems IRIS issues an SQLCODE -30 error.
  • You must have DELETE privilege for the table. Failing to have this privilege results in an SQLCODE -99 (Privilege Violation) error. 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.CheckPriv() 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 the Other Options for Persistent Classes chapter of Defining and Using Classes.
  • If deleting through a view, the view must be updateable; it cannot be defined as WITH READ ONLY. Attempting to do so results in an SQLCODE -35 error. See the CREATE VIEW command for further details.
  • 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. If a row cannot be locked, TRUNCATE TABLE fails to delete any rows and issues an error. If deleting a row would violate foreign key referential integrity, TRUNCATE TABLE fails to delete any rows and instead issues an SQLCODE -124 error. This default behavior is modifiable, as described below.

Fast Truncate

When possible, the SQL optimizer performs a highly efficient Fast Truncate table operation. A Fast Truncate operation deletes the table’s extent, rather than deleting each record individually. Fast Truncate is automatically applied where possible. When Fast Truncate is not possible, a standard TRUNCATE TABLE operation is performed.
Note:
TRUNCATE TABLE does not initialize or set %ROWID if no rows are deleted, or if rows are deleted using Fast Truncate. Therefore, the use of the %ROWID value following a TRUNCATE TABLE should be avoided.

Fast Truncate Restrictions

Fast Truncate can be applied to standard table or a sharded table.
Fast Truncate cannot be applied:
  • If the user is unable to acquire a table-level lock (unless %NOLOCK is specified).
  • If the table is the target of a foreign key constraint.
  • If the table contains a stream field with a specified LOCATION parameter. Fast Truncate can be applied when all stream fields do not specify the optional LOCATION parameter.

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 row being deleted. This should only be used when a single user/process is updating the database. If you do not specify %NOLOCK, a fast truncate attempts to acquire a table-level lock. If TRUNCATE TABLE cannot acquire a table-level lock, it performs a standard truncate table, acquiring row-level locks on each row of the table.
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 configuration setting to determine whether to perform foreign key referential integrity checking. You can set this system default using the $SYSTEM.SQL.SetFilerRefIntegrity() method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings(). The default is “Yes”. If you change this setting, any new process started after changing it will have the new setting.
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. This means that 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 GetLockThreshold() method. This system-wide lock threshold value is configurable:
  • Using the SetLockThreshold() method.
  • Using the Management Portal. Go to System Administration, Configuration, SQL and Object Settings, SQL. View and edit the current setting of Lock escalation threshold.
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 in the “Modifying the Database” chapter of Using InterSystems SQL.

Imported SQL Code

The DDLImport("IRIS") and IRIS() 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.

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 %StatementTypeName 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

Previous section   Next section