The create table command supports the ability to remove residual data from deletions in SAP ASE.
create table table_name with "erase residual data" {on | off}
Example 1
The option to erase residual data is turned on for table t1 because it is set at the database level, so that both the drop table and truncate table commands for t1 result in the cleanup of all residual data from its pages.
create database db1 go sp_dboption db1, "erase residual data", true go use db1 go create table t1 (col int) go insert t1 values ... go create table t2 (col1 int, col2 char(10)) with "erase residual data" off go truncate table t1 go drop table t1 go truncate table t2 go drop table t2 go
Example 2
create database db1 go use db1 go create table t1 (col int) go sp_dboption db1, "erase residual data", true go create table t2 (col1 int, col2 char(10)) go create table t3 (col1 int, col2 char(10)) with "erase residual data" off go truncate table t1 go truncate table t2 go truncate table t3 go
Example 3
create database db1 go use db1 go create table t1(col int) go create table t2 (col1 int, col2 char(10)) go create table t3 (col1 int, col2 char(10)) with "erase residual data" off go set erase_residual_data on go truncate table t1 go truncate table t2 go truncate table t3 go
When you set this option on a table, the operations for the table (drop table, delete row, alter table, drop index) that result in residual data automatically clean up deallocated space.
Only the table owner or a user with create any table permission can use the "erase residual data" option.