6

So far I have been using SQL Server Express on my desktop application even though usually the server is only used by that single application, by the single user, on the same single machine. This always seemed a bit silly to me since the full-on server is rather heavy.

Then I found out that there is something called SQL Server Compact. Thing is, my application is already rather large. So my question is that if I wanted to change to SQL Server Compact, what kind of changes to my code are we talking about (using C#)?

Mainly I'm wondering if I can access the Compact -version in the same manner as I can access the Express one, which is using ADO.NET and pointing it towards localhost\sqlexpress. So would it be possible to create another instance with the Compact and just point my application to that, or is it used in a completely different manner?

MPelletier
  • 16,256
  • 15
  • 86
  • 137
bobblez
  • 1,340
  • 20
  • 35

4 Answers4

10

For a standalone desktop product it is certainly a lightweight option to consider over SQL Express.

However there are differences between the two to be aware of other than just connection strings. For example SQL CE does not support stored procedures, user-defined functions, or triggers. Additionally it does not support the full range of datatypes that Express supports. On a technical level it also runs in process with your application.

Another consideration is that while SSMS will work with SQL CE databases, it's not as fully featured as it is with SQL Express. There are however a whole raft of good tools out there for filling these gaps. Take a look at Erik Jensen's blog for a good overview.

Everything SQL Server Compact

Also check out his tool for migrating SQL Express databases to SQL Compact:

How to Migrate/Downsize a SQL Server database to SQL Server Compact 4.0 (and 3.5)

Finally take a look at this SO question for more background between the two products:

What are the limitations to SQL Server Compact? (Or - how does one choose a database to use on MS platforms?)

Community
  • 1
  • 1
Ira Rainey
  • 5,173
  • 2
  • 34
  • 42
1

I worked recently with it, by the way the things I know about using them is that SQL Express has a server to connect to, and, Compact is a sdf file, so the first thing you will have to change is the connection string to it.

After this change, there are no more heavy changes to be done, I remember, compact has almost all the instructions of the express server available, so, it could not be much problem.

Take a look at Microsoft documentation for more information, or at Wikipedia.

See you.

Amedio
  • 895
  • 6
  • 13
1

I would vote against SQL CE:

  1. It has no views, which might be an issue when migrating.
  2. We recently did something similar which you described and had tremendous performance impacts when switching from SQL Server Express to SQL Server Compact Edition.

My recommendations would be:

  1. Use SQLite (which we did for the project I mentioned - it was much more performant than SQL CE in our case) -or-
  2. Use VistaDB (which I did in other projects; not as performant as SQL Server Express, but still sufficient)

Both databases can be XCOPY deployed, just like SQL Server Compact Edition.

Community
  • 1
  • 1
Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
0

SQL CE has the same size limitation as Express, so you should be good.

As far as moving between the two, I found this for moving between express databases and compact 3.5 databases. Then I think you'll only have to change your connection strings (instead of pointing at a host/instance, you point at the converted file). There are different features between the two, though, so you might have to change your schema in the original database for the conversion to go smoothly.

Tim Coker
  • 6,484
  • 2
  • 31
  • 62