Switching from using SQL Server Express to SQL Server Compact

Question

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?

Answer 1

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?)

Answer 2

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.