Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Disadvantages of using SQL Server Compact versus dedicated SQL Server database

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?

like image 739
subZero Avatar asked Jun 28 '12 10:06

subZero


People also ask

Is SQL Server Compact deprecated?

In February 2013, Microsoft announced that SQL Server Compact Edition had been deprecated. Although no new versions or updates are planned, Microsoft will continue to support SQL Compact through their standard lifecycle support policy. Extended support for SQL Server Compact 4.0 ended on July 13, 2021.

What is the use of SQL Server Compact?

Microsoft SQL Server Compact 4.0 is a free, embedded database that software developers can use for building ASP.NET websites and Windows desktop applications.

What is Microsoft SQL Server Compact 3.5 used for?

SQL Server Compact 3.5 SP2 is an embedded database that allows developers to build robust applications for Windows desktops and mobile devices.


1 Answers

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!

like image 77
Alexander Schmidt Avatar answered Sep 21 '22 01:09

Alexander Schmidt