I just thought I'd share my findings in choosing between SQL server Compact and Express. What are they for, and which one should I use in what scenario.

In short this paper explains that if you need any of:
- Multiple concurrent users
- Stored procedures
- Views
- Triggers
- Procedural T-SQL
- Native XML
...then you should go for the EXPRESS version.

If you do not need any of the above but do need any of:
- Less than 2 MB download size
- Embedding in your application
- To run it on a mobile device (which implies that considerably less resources are used by the compact version compared to the express version when running on a PC)
...then you should go for the COMPACT version.

I'd say that if you're developing a small single user application and don't plan to use any fancy database stuff then the compact version will do the job very nicely. As soon as you start to think "a few users" or "well, this can be more smoothly or elegantly solved if I just use <insert non-trivial database stuff here>" then go for the express version.

Hope this sorts things out for someone more than me.

/Jens

PS: I just found a note on a few things that may come in handy if you want to use the compact version.


This post has been edited by jens: 7 Oct, 2008 - 12:35 AM

Hello ,

Has anyone tried using <BEGIN TRY> in SQL 20054 EXPRESS ?

i cannot get it to work and have reverted back to sql 200 style. Can anyone help?

/jonesta

This post has been edited by Jonesta: 13 Oct, 2008 - 03:46 AM

I have verified the following code

in SQL server 2005 EE and it gives a totally different result than

so I'd say it works.

More info here.

Hope this gives a clue.

/Jens