Table Containing Data
By default, DROP TABLE deletes the table definition and deletes the table’s data. This table data delete is an atomic operation; if DROP TABLE encounters data that cannot be deleted (for example, a row with a referential constraint) any data deletion already performed is automatically rolled back, with the result that no table data is deleted.
You can set the system-wide default for table data deletion using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method DDLDropTabDelData option. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays the Does DDL DROP TABLE delete the table's data? setting.
The default is 1 (“Yes”). This is the recommended setting for this option. Set this option to 0 (“No”) if you want DROP TABLE to not delete the table’s data when it deletes the table definition.
The deletion of data can be overridden on a per-table basis. When deleting a table, you can specify DROP TABLE with the %NODELDATA option to prevent the automatic deletion of the table’s data. If the system-wide default is set to not delete table data, you can delete data on a per-table basis by specifying DROP TABLE with the %DELDATA option.
In most circumstances DROP TABLE automatically deletes the table’s data using a highly efficient kill extent operation. The following circumstances prevent the use of kill extent: the table has foreign keys that reference it; the class projecting the table is a subclass of a persistent class; the class does not use default storage; there is a ForEach = "row/object" trigger; there is a stream field that references a non-default stream field global location. If any of these apply, DROP TABLE deletes the table’s data using a less-efficient delete record operation.
You can use the TRUNCATE TABLE command to delete the table’s data without deleting the table definition.
