The many versions of SQL Server CE

If you refer to the 70-540 Exam study guide you will see references to a database product called SQL Server 2005 Everywhere Edition. This product technically doesn’t exist!

Logo of SQL Server 2000 Windows CE Edition product

Originally there was a database product for Pocket PC devices called SQL Server CE (aka SQL CE). For similiar reasons to those mentioned in my blog post about Windows Mobile naming conventions, the product name for the SQL Server CE database engine has changed over time.

This leads to a confusing mismatch of names through out the various developer tools and documentation, especially when Visual Studio and SQL Server CE releases were not in sync with each other. As a simple example the ADO.NET data provider for SQL Server 2005 Mobile Edition databases still lives within the System.Data.SqlServerCe namespace for backwards compatability reasons.

A brief version history

There have been atleast 5 major releases of the SQL Server CE database engine as follows.

Offical Name Nickname
SQL Server 2000 Windows CE Edition SQL CE 1.0
SQL Server 2000 Windows CE Edition SQL CE 1.1
SQL Server 2000 Windows CE Edition SQL CE 2.0
SQL Server 2005 Mobile Edition SQL CE 3.0
SQL Server 2005 Compact Edition* SQL CE 3.1
SQL Server Compact 3.5 SQL CE 3.5

* Was also called SQL Server 2005 Everywhere Edition.

Visual Studio 2005 originally came with support for SQL Server 2005 Mobile Edition. However with Service Pack 1 (and an optional download) it is possible to update it to support development against SQL Server Compact Edition.

The 70-540 Study Guide was obviously produced before SQL Server Compact Edition was officially released. Prior to final release the product was scheduled to be named SQL Server Everywhere edition.

There is little difference between SQL Server 2005 Mobile Edition (v3.0) and SQL Server 2005 Compact Edition (v3.1) on PDAs, the major difference is that SQL Server 2005 Compact Edition was the first release to support running the database engine on an X86 desktop PC. Prior to this release SQL Server CE only supported running on Windows CE based devices such as Pocket PC PDAs.

I am about to renew my effort to post blog entries for my 70-540 Study Guide. This blog post was designed to setup the stage for a series of posts about database access within the .NET Compact Framework environment.

10 Responses to “The many versions of SQL Server CE”

  1. It’s worth mentioning that all these versions have three different and incompatible file formats. If I remember correctly, versions 1.0, 1.1 and 2.0 share the same format. Versions 3.0 and 3.1 have a second format and version 3.5 has another format.

  2. That’s a great point, thanks for contributing it. I’ll mention this in an upcomming blog post where I discuss the tooling support (Visual Studio designers etc) for the various database versions.

    Another thing to watch out for with respect to format/structure of SDF database files is apparently the sort order of indexes within the database file. This was discussed on Jim Wilson’s blog today. This can affect the initial performance of opening a database file initially created on the desktop on a PDA in certain scenarios (as it forces an index rebuild).

  3. Miguel says:

    Hi, thanks for the post. Really useful to let me definitively identify all versions of SQL Mobile databases.

    We’ve got an application that uses Merge Replication to synchronize SQL Server 2005 Mobile (SQL CE 3.0 from your list) databases with an SQL Server 2005 backend server. Although they’ve published newer databases, just in case won’t upgrade my application. Sure an upgrade would make me some trouble.

    Final thought: Why does Microsoft publish so many products in such a short period of time, and why does change so many times the names of its products? I’ll never understand this…

  4. Hi Miguel,

    A blog post that might help in your situation is the Connectivity cross version compatibility post on the SQL Server Compact Team blog.

    This covers the compatibility of the various merge replication and RDA components between the different versions of SQL Server Compact Edition. It is however a bit of a “scary” read, you’ll want to be sitting down and have a couple of minutes to spare to comprehend it…. Jeff Wharton has a condensed summary available on his blog in a post called “SQL Server Compact 3.5 – Cross Version Comptibility“.

    By the sounds of it you could upgrade your server side bits to v3.5, while still using v3.0 on the client devices. This may allow you to do a phased upgrade for example.

    I’m not a big fan of the various tools (from the outside atleast) appearing to be tightly coupled either. It’s a pain needing to change versions of IDE just to get full support for a newer version of the database engine. From the outside it would seem logical that database support could be abstracted out to allow the one IDE to make use of a different database engine version.

  5. Marco Kummer says:

    Hi Christopher

    Thanks for clearing things up about the many versions of SQL CE! I had a huge problem trying to get a third party component to work with VS 2008 and couldn’t figure out why it wouldn’t work.. until I found your entry!

    http://www.adapt.ch/Blog/2008/02/21/SQLServerCompact35WithXPO737.aspx

    Keep up the good work!

  6. Le Sage says:

    On the contrary to what is written on the SQL Server CE 3.5 download page, it perfectly works witj VS05 SP1 (VS08 isn’t required).

  7. Raymund Banaag says:

    Hi Christopher,

    I was on the middle of my R & D (Research and Development) on developing application on windows mobile 5.0 using visual studio 2005 and .net CF v2.0. I made a simple program wherein you can add a record and retrieve it.

    No problem or Error whenever I run it or deploy it using the pocket pc emulator but when run it in Actual Pocket PC and when I click the Add New Button (which would add a record to the Database), an error will show up (”File or assembly name ‘System.Data.SqlServerCe, Version = 3.0.3600.0, Culture = neutral, PublicKey Token=3BE235DF1C8D2AD3′, or one of its dependencies, was not found.”).

    I am so close to achieve my R & D and develop a fully running application on pocket pc but this error stops me from here.

    I hope you can give me some advice or help me with this problem.

    Thanks a lot in advance,

    Raymund Banaag
    Systems Developer
    Cebu City, Philippines

  8. [...] SQL auf dem MDA In Sachen Versionsnamen gibt’s z.B. hier http://www.christec.co.nz/blog/archives/236 eine Zusammenstellung. Ist das ein einmaliger Prozess oder sollen die DBs gegenseitig aktualisiert [...]

  9. HemaLatha says:

    Hi,

    Thanks for your articles.These kind of articles are Very useful. Could you please clarify me.Is it possible to insert images into Sql Server CE in mobile. Where do we find Sql Server CE Database in mobile?

  10. Dabby says:

    Tx, But which one is SQL 2008, bcos u did not mention it in ur diff versions of SQL?

Leave a Reply