More on SQL Server Compact Indexes and Sort Order

Last week I posted about the affects of SQL Server Compact's (SSC) usage of the host computer's sort-order handling ... SSC may need to rebuild the indexes in an existing database if the database is created on computer that uses one set of rules for sort order and then is moved to a computer with different sort order - For example Vista & Windows Server 2008 use different sort order rules then earlier Windows version such as Windows XP and Windows Mobile.

I've received feedback from a few people talking about SSC's need to rebuild the indexes being poor design. While I understand this position, especially if one is not familiar with the nature of an in-process database solution, I must disagree. I've replied to each of these folks individually but I thought I'd take a minute and repeat those comments here in a general post...

One of the points often raised is that the full version of SQL Server 2000/2005 is clearly better designed because it does not have to use the host computer's sorting rules; SQL Server in fact allows you to choose the sorting rules for a particular database instance. The thing to remember is that SQL Server is a self-contained server-process 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 think one needs to consider the trade-offs that must be evaluated in implementing a tool like SSC....

The entire SSC installation takes up less than 2MB. Maintaining such a small footprint requires one to make trade-offs. I think the choice to use the host computer's sort-order APIs rather than have SSC 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 that is upwardly compatible with full SQL Server; SSC is not intended to be a little version of the great-big SQL Server engine.

Another key point to consider - When using a database system that runs in-process to an application, one would normally expect the database to order data consistent with other sorted data stored in the  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 computer's 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.

Although the need for SSC to sometimes rebuild the indexes when moved between different Windows version is not ideal, I think that one can make a pretty strong argument that using the host computer's sort order API's is at a minimum a good choice and possibly the best choice when one considers the nature of the product's usage and goals.