From SQLite to SQL Server Compact Edition

time to read 3 min | 546 words

I am giving up on SQLite as my embedded DB of choice. The reason for this is very simple. I really want to be able to use the in memory features (and other nice stuff it has to offer), but it has the worst support for date/time manipulations that I have seen. I am trying to formulate a query that should be along the lines of:

select  *

from    blogs,


where   posts.blogId =

        and ( + blogs.maxDaysInFeed ) <= getdate() ;

As much as I dislike dates, I tend to work with them in complex ways quite a bit, and I really need a database that can work with me in this regard. I just finished moving my in memory infrastructure to using SQL Server Compact Edition. This means that my tests will have to touch the disk, but I am willing to make this compromise.

In favor of Sql CE are the following:

  • Embedded DB, so it is faster (nor RPC call)
  • It has a behavior that is very similar to SQL Server, so I can test on Sql CE and then move to use SQL Server with worrying about the differences.
  • I can use Management Studio to query it! Killer feature to say the least.