I've developed a .NET WPF application, with data storage handled by SQL Server Compact Edition. I noted that when I zip the obtained SQL Server CE file, I often compress it down to 5% of the original file size.
At the risk of showing some significant ignorance here, is this expected behaviour? It seems that given this massive size saving, I should be compressing the files every time I save (I have not yet considered the performance implications of this). Database size can vary from a few MBs all the way to the maximum 4GB, although this will be rare. Is my data being stored inefficiently, or why am I getting such significant savings when I compress? Is there something that I can do to always obtain this space saving, without having to externally compress the SQL Server CE file?
A database shouldn't be compressed since it will hugely impact performance of retrieval. I will give you an example.
If a field is 200 bytes long, the database reserved 200 bytes in order to save a value on a specific row. It can calculate the total row size adding the length of all fields.
A compression algorithm might compress the actual value in it to almost nothing. However, if a database would do that, it couldn't rely on calculating the position on disk (row number * row size + column offset). It first has to decompress everything in order to get the value of a specific column on a specific row. You don't want that for a performance point of view.