What is the difference between Shrink and Compact in SQL Server CE?

Question

I have a method that is run periodically to optimise my application's SQL Server Compact Edition (3.5) database. The existing code uses the Shrink() method:

        SqlCeEngine engine = new SqlCeEngine(dbConnectionString);
        engine.Shrink();

Today I noticed that there's also a Compact() method. Which is preferable for periodic maintenance?

Answer 1

From the SQL Server Compact Team Blog:

The difference between these two is much similar to Internal and External Memory Fragmentation.

From SqlCeEngine.Shrink documentation,

Reclaims wasted space in the database by moving empty and unallocated pages to the end of the file, and then truncating the file. You can configure a database to automatically shrink by setting the autoshrink threshold option in connection string. Shrink does not create a temporary database file.

From SqlCeEngine.Compact documentation,

Reclaims wasted space in the database by creating a new database file from the existing file. By creating a new database means, it reclaims the free space between rows.

To be more clear, Shrink claims the pages which are entirely free or unallocated; where as, Compact claims the wasted space with in the page too. Hence Compact requires creating a new database file.