Caché SQL Reference
TRUNCATE TABLE
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Removes all data from a table and resets counters.
Synopsis
TRUNCATE TABLE tablename
Arguments
tablename The table from which you are deleting all rows. You can also specify a view through which table rows can be deleted. For further details, see FROM.
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.
The TRUNCATE TABLE operation resets the internal counters used for generating ID field, IDENTITY field, and %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 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
The TRUNCATE TABLE operation sets the %ROWCOUNT local variable to the number of deleted rows, and the %ROWID local variable to the Row ID of the last row deleted.
Note:
The DELETE command provides more functionality than TRUNCATE TABLE, and is the preferred command for deleting data from a table. TRUNCATE TABLE is provided for compatibility for code migration from other database software.
To truncate a table:
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:
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" }
 
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:
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 systemwide lock threshold value using the GetLockThreshold() method. This systemwide lock threshold value is configurable:
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:
  ZNSPACE "SAMPLES"
  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,!

  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," last rowID: ",rset.%ROWID,!

  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," last rowID: ",rset.%ROWID,!

  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," last 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 to 0:
  ZNSPACE "SAMPLES"
  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,!

  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," last rowID: ",rset.%ROWID,!

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

  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," last rowID: ",rset.%ROWID,!
  &sql(DROP TABLE SQLUser.MyStudents)
 
See Also