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.
+3
A:
Please see:
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
Mitch Wheat
2009-10-18 11:21:37