Monday, January 26, 2009

Tip #36 – Pack and Zap Tables Using SQL

Records which are deleted from DBF tables are not removed from the file they are marked as deleted. To remove these deleted records from the table a pack operation is required. Tables can be packed using the ACE API, TDataSet Descendent, .NET Data Provider or via the system procedure sp_PackTable. The pack removes all deleted records from the table and re-indexes the table.

Like DBFs Advantage Database Tables (ADTs) do not physically remove deleted records from the table. Instead deleted records are marked for reuse so a subsequent insert operation will not increase the size of the file. You can get more details from the help file.

You can delete all records in the table using the DELETE command, however, these records are simply marked for deletion. To physically remove the records you would then have to pack the table. You can substitute these two commands with a zap command which deletes all records then packs the table. To zap a table use the system procedure sp_ZapTable.

Examples of these functions are below:

// Physically remove deleted records and re-index
EXECUTE PROCEDURE sp_PackTable('C:\Data\Custtemp.adt')
// Permanantly remove all records from the table
EXECUTE PROCEDURE sp_ZapTable('C:\Data\Custtemp.adt')

1 comment:

Anonymous said...

Hi Chris,

how can i do an "execute procedure" in my .NET solution?
(I use the OLEDB Provider, i can mark as deleted the records from a DBF table, but i don't know how execute this procedure to physcally remove the records)


(Gabriel from Spain)