This Tech Tip serves as a reminder to compact your database file after using the Drop Table SQL command. Once a table is deleted after using the command, the data file still contains the corresponding data, but the data is no longer accessible even if the table and its fields are recreated to be exactly the same. Compacting the data file will clear the records that corresponds to the deleted table which will result in a smaller file size and some increase in performance.

Example:

After creating a table of 500,000 records:



(My table is comprised of one field of sequential numbers, and another field of randomly generated numbers)

...my data file file is rather large, it contains 500,000 records:

On Macintosh:


On Windows:


After removing the table with the DROP TABLE command:
Begin SQL
DROP TABLE [Table_1]
End SQL

...my table is gone, but I am still left with the large data file size, even after closing 4D and re-opening. The data from the table is now inaccessible, but still exists.

After recreating the table with the exact same name and fields:



I now have an empty table with 0 records.

As you can tell, my data is inaccessible now and the data file size remains the same.

In Design mode, if you click on MSC in the upper right hand corner:



..you are presented with a window which has alerts that are brought to your attention. If you go to the Compact tab, you see an area describing the overall size of your database files, and a strong recommendation to compact your data file.



Click on the large button at the top resembling a filing cabinet for "Compact records and indexes" and 4D will notify you that the database will close and reopen to complete the compact. Select OK and wait a few moments...

During a compact, 4D copies the original files and puts them in a folder named "Replaced Files (Compacting)", which is created next to the original file. When the operation is completed, the compacted files automatically replace the original files. The database is immediately operational without any
further manipulation. The end result is a data file with a smaller file size.

Other Resources:
4D v11 SQL Maintenance and Security Center