6

I'm wondering what the key differences between using a SQL Server Compact database (.sdf) and a full fledged database like for example SQL Server Express are?

Are there any major performance issues, and if so, how big can the compact database get before starting to notice this?

When starting projects I find using a compact database a simple, straight forward and clean solution, when should I convert and move over to a dedicated database?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
subZero
  • 5,056
  • 6
  • 31
  • 51

3 Answers3

11

Lets try:

  • max 2 GB file size
  • No stored procedures, triggers etc.
  • No process but loaded into your AppDomain
  • As far as I know, there is no cost based optimizer or query plans for queries
  • Lack of concurrent access of multiple users at the same time

The big issue here is, that CE is only a file on your system and you get access through a simple InApp-call using a dll. Thats it and in many scenarios this is enough. Many people would say, that you can switch to SQLS later but I don't think so. It's a complete different world! CE is a single product in my eyes.

Remember, that you need to deploy the CE-DLL when you wan't to publish your app!

Alexander Schmidt
  • 5,631
  • 4
  • 39
  • 79
  • 5
    Great list. I would add that SQL Server Compact doesn't have any in-memory cache, so all queries hit disk all the time. With SQL Server Express hot data stays in memory which can speed queries up significantly, especially if the queries are run often. – Krzysztof Kozielczyk Jun 28 '12 at 16:02
  • It is actually max 4 GB per database and there is a cost based Query processor and query plans. And also APIs to "avoid" the query processor – ErikEJ Apr 27 '15 at 08:32
3

See my comparision chart here: http://erikej.blogspot.dk/2011/01/comparison-of-sql-server-compact-4-and.html

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
2

SQL Compact doesn't support Stored Procedures. You write all of your query directly in code. That's enough for me to choose the SQL Express. Now, we have also the option of LocalDB that simplify the deployment scenarios.

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286