May 4th, 2010

No Clustered Indexes in SQL Server Compact

Contrary to what my copy of “Microsoft SQL Server 2005 Compact Edition” (Dhingra & Swanson, ©2008 SAMS Publishing) says twice on page 513, SQL Server Compact Edition only supports NONCLUSTERED indexes.

Clustered indexes aren’t always absolutely necessary, but for some queries they increase retrieval speed significantly. Having spent several weeks tuning Glo’s content database for performance, I was a little discouraged to read about this lack of support:

http://technet.microsoft.com/en-us/library/ms345331.aspx

The reference above is from 3.5 SP1, and the SP2 docs don’t allude to a change in this regard. Fortunately most (but not all) of Glo’s queries run extremely fast — sub-millisecond in places.

2 Responses to “No Clustered Indexes in SQL Server Compact”

  1. Just out of interest – why did you go for MS SQL Server, and not sqlite? It may have made coding for other platforms (iPhone etc) easier! (Just curious, as I’m wondering what to have as a backend DB)

    Thanks,

    Adrian

  2. Rob says:

    The short answer to Adrian’s question is, for quick .NET compatibility, ease of installation, and speed. Although SQL Server Compact Edition can’t compare to full-blown SQL Server, it is what Microsoft pushes for embedded desktop apps like Glo. And I’ve been happy with its speed and API.

    I considered other candidates like SQLite & VistaDB. Valentina was also a strong contender because I loved using it while writing iLumina. I even considered using SQL Server Express for its fuller feature set, but didn’t want to actually install that on user’s machines. SQL Server Compact ended up feeling the most like what I needed for Glo Windows desktop version.

    We’re also developing with SQLite on the non-Windows desktop side, although the source for both remains SQL Server 2008.

Leave a Reply