SQL CE - Pros & Cons

1 minute read

The new SQL CE for the mobile applications has some striking features. In this post, let me list some of its advantages & disadvantages:

Advantages:

  • Target Applications: Can be used in Desktop/Web/Mobile applications.
  • Size: Very tiny footprint: 1.7MB
  • Operating Systems: Win XP, 2003, Vista, Mobile or any SmartPhone.
  • Continues to be a single-file database
  • Involves no cost! Just register yourself on the Microsoft site.
  • Easy in deployment.

Disadvantages:

  • Does not support all features of MS-SQL 2005.
  • Conflicting Statements:
  • Stored Procedures are NOT supported :(
  • Stored Procedures are SUPPORTED :)
  • Difference in the syntaxes
  • Maximum size of database : 4 GB.

A note on disadvantages:

The above disadvantages have been listed considering the fact that SQL CE is used for Desktop/Web applications. However, SQL CE was launched for Mobile Applications so the disadvantage of 4 GB is hardly a disadvantage - wonder if there are smart devices that can support 4+ GB of data!

One serious disadvantage is : NO Stored Procedures calls can be made, which makes it equivalent to MS-ACCESS.

SqlCommand cmd = new SqlCommand();
cmd.CommandText = “MyStoredProc '02/23/2004' ”;
cmd.CommandType = CommandType.StoredProcedure;

is not allowed

Alternative to use Stored Procedure call:

SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess();
rda.Pull("authors", "exec MyStoredProcName '02/23/2004' ", ...);

is allowed!

So, to hook SQL CE database to a desktop application that handles smaller chunk of data appears to be a good solution. However if the data becomes large, you can break the data into multiple database files, which is anytime easier than maintaining large XML, CSV or Access database files.

Updated: