create table for Residual Data Removal

The create table command supports the ability to remove residual data from deletions in SAP ASE.

Syntax

The syntax to specify this in a new table is:
create table table_name
    with "erase residual data" {on | off}

Examples

The following examples use these two tables:
  • create table t1 (col1 int) with "erase residual data" on
  • create table t2 (col1 int) with "erase residual data" 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.

Table t2, however, has the erase residual data option turned off explicitly, as it was created with the "erase residual data off" clause. Residual data is not removed, even though the "erase residual data" option is set to true at the database level. As a result, residual data remains, even after running drop table and truncate table on t2:
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

In this example:
  • Table t1 does not have "erase residual data off" set explicitly, but does have it set at the database level, resulting in the removal of residual data from t1 when you run truncate table t1.
  • Table t2 has the "erase residual data" option set at creation, because the option was set at the database level. This results in the removal of residual data from t2 when you run truncate table t2.
  • Table t3 is marked with "erase residual data off" explicitly, so that even though sp_dboption sets "erase residual data" to true, residual data is not removed when SAP ASE runs truncate table t3.
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

In this example:
  • Although both t1 and t2 tables had the "erase residual data option not set by default, because "erase_residual_data was turned on at the session level just before the truncate table command was executed, the residual data is removed on both t1 and t2.
  • Although table t3 has the "erase residual data" option explicitly set to off, residual data is still removed when the truncate command is executed, because the "erase_residual_data" option is set at the session level.
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

Usage

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.

Permissions

Only the table owner or a user with create any table permission can use the "erase residual data" option.

Related concepts
set
sp_dboption