SQL Server Compact - May unexpectedly rebuild table indexes when database first opened

Actually it does not have to match. Full version of SQL carries its version of sort order along and thus full SQL Server behaves identically on all platforms. Sort order may change from version to version though (e.g. SQL 2000 to SQL 2005).

SQL Compact exhibits poor design and lack of consideration for the user. It burdens the user with something that should never be user's concern. Boo.

Denis;

In the full version, you're right, sort-order doesn't have to match the host. That's because Full SQL Server is a self-contained server allowed to occupy many, MANY megabytes of disk-storage as well as many megabytes of RAM. Full SQL Server can take up pretty much as much memory as it wants; as a result, providing a selectable sort-order that's internally implemented is a good idea.

In terms of calling SSC index management "poor design", I really think it's a matter of trade-offs. The entire SSC installation takes up less than 4MB. Maintaining such a small footprint requires one to make trade-offs. I think using the host sort-order APIs rather than incur the overhead of implementing and managing custom sort-oriented APIs is a reasonable one. Remember, SSC is intended to provide an application with locally accessible, reliable storage; it’s not intended to be a little version of the great-big SQL Server engine.

Another key point to consider; if you’re using a database system that runs in-process to your application, wouldn’t you expect the database to order data in the same order as other sorted data you store in your application? Take this scenario...
- You have an array of randomly entered strings
- You write the array contents to a database table that is indexed on those values
- You sort the array in memory using Array.Sort or similar

Wouldn’t you expect the array and database table to return the contents in the same order? If SSC didn’t use the host sort-oriented APIs, in some cases the database table order would differ from the array order. I think most of us would consider that an error.

I think we can agree that it’s a matter of choosing the right tool for the job.

If one needs the flexibility to control various aspects of the data storage such as sort-order consistency across platforms, along with a boat-load of other tunable features, then one should choose Full SQL Server – and pay the memory cost and overhead that goes with that.

If one needs a database that’s highly portable, works well on both a mobile device and a desktop computer, does not require any software to be installed other than the app using it, does not require network connectivity to access it, and consumes minimal resources then one should choose SQL Server Compact – and accept that they may not have every possible feature available.

Neither way is right or wrong, just solving different problems.

Jim :-)

Well, those are some very convincing arguments.
I admit I did not think it trough.

It still pains me to see the user impact being of what is essentially an optimization being so large.

Denise;

"It still pains me to see the user impact ... being so large"

I couldn't agree more.

Jim :-)