shrink or compact in Sql Server CE?

Question

I am using Visual Studio 2008. and i am new at sqlce.I am checking sdf file and if sdf doesnt exist,I create and then insert around 5000 rows to my sqlce db.If sdf exists,first i delete all tables's rows and then insert these records to existed tables.After insert complete,i am compacting database.By the way,compacting lasts about 13 seconds.I know a method that calls Shrink.Shrink and Compact make db smaller but in this scenerio which of them should i use?How can i make compacting withing shorter time?I dont need backup of sdf and after compact,i delete old sdf.i read this topic but couldnt decide which i should use.

Answer 1

Please see:

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

which quotes from the SQL Server Compact Team Blog:

SqlCeEngine/ISSCEEngine: Shrink Vs Compact 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.

Empty space with in the page could be as result of:

1) If there were 5 rows in a page and two of them are deleted

2) If there was a row in the middle of the page which on update required more space, has moved out of the page (Ex: nvarchar column update)

Empty pages and unallocated pages could remain in database as a result of:

1) All rows in a page are deleted

2) Whole table is dropped