docs.intersystems.com
InterSystems IRIS Data Platform 2019.2  /  InterSystems SQL Reference  /  SQL Commands

InterSystems SQL Reference
TRUNCATE TABLE
Previous section           Next section
InterSystems: The power behind what matters   
Search:  


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.
The TRUNCATE TABLE operation 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 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, but does not reset internal counters. TRUNCATE TABLE provides compatibility for code migration from other database software.
To truncate a table:
Fast Truncate and %ROWCOUNT
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.
If the optimizer performs a Fast Truncate, the TRUNCATE TABLE operation cannot set the %ROWCOUNT local variable to the number of rows deleted, and instead sets %ROWCOUNT = -1. If the optimizer cannot perform a Fast Truncate, the TRUNCATE TABLE operation sets the %ROWCOUNT local variable to the number of rows deleted.
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:
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" }
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:
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:
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
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-09-20 05:47:55