Skip to main content
Previous sectionNext section

TRUNCATE TABLE

Removes all data from a table and resets counters.

Synopsis

TRUNCATE TABLE tablename

Arguments

Argument Description
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 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. Caché 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 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
Copy code to clipboard

TRUNCATE TABLE sets the %ROWCOUNT local variable to the number of deleted rows.

TRUNCATE TABLE sets the %ROWID local variable to the RowID of the last row deleted. However, TRUNCATE TABLE does not initialize or set %ROWID if no rows are deleted. Therefore, the use of %ROWID with TRUNCATE TABLE should be avoided.

Note:

The DELETE command can also be used to delete all rows from a table. DELETE provides more functionality than TRUNCATE TABLE, but 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, Caché issues an SQLCODE -30 error.

  • 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.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 Using Caché Objects.

  • 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.

Atomicity

By default, TRUNCATE TABLE, DELETE, UPDATE, and INSERT are atomic operations. A TRUNCATE TABLE either completes successfully or the whole operation is rolled back. If any row cannot be deleted, none of the rows are deleted and the database reverts to its state before issuing the TRUNCATE TABLE.

You can modify this default for the current process within SQL by invoking SET TRANSACTION %COMMITMODE. You can modify this default for the current process in ObjectScript by invoking the SetAutoCommit() method. The following options are available:

  • IMPLICIT or 1 (autocommit on) — The default behavior, as described above. Each TRUNCATE TABLE constitutes a separate transaction.

  • EXPLICIT or 2 (autocommit off) — If no transaction is in progress, a TRUNCATE TABLE automatically initiates a transaction, but you must explicitly COMMIT or ROLLBACK to end the transaction. In EXPLICIT mode the number of database operations per transaction is user-defined.

  • 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.

You can determine the atomicity setting for the current process using the GetAutoCommit() method, as shown in the following ObjectScript example:

  DO $SYSTEM.SQL.SetAutoCommit($RANDOM(3))
  SET x=$SYSTEM.SQL.GetAutoCommit()
  IF x=1 {
    WRITE "Default atomicity behavior",!
    WRITE "automatic commit or rollback" }
  ELSEIF x=0 {
    WRITE "No transaction initiated, no atomicity:",!
    WRITE "failed DELETE can leave database inconsistent",!
    WRITE "rollback is not supported" }
  ELSE { WRITE "Explicit commit or rollback required" }
Copy code to clipboard

Referential Integrity

Caché uses the system configuration setting to determine whether to perform foreign key referential integrity checking. You can set the system default as follows:

  • The $SYSTEM.SQL.SetFilerRefIntegrity() method call.

  • Go to the Management Portal, select System, Configuration, General SQL Settings. View and edit the current setting of Perform Referential Integrity Checks on Foreign Keys for INSERT, UPDATE, and DELETE. 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

Caché 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 Caché 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, Configuration, General SQL Settings. View and edit the current setting of Lock Threshold.

You must have USE permission on the %Admin Manage Resource to change the lock threshold. Caché 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 Caché SQL.

Imported SQL Code

The DDLImport("CACHE") and Cache() 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)
Copy code to clipboard

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)
Copy code to clipboard

See Also