Ayende @ Rahien

My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:


+972 52-548-6969

, @ Q c

Posts: 6,007 | Comments: 44,760

filter by tags archive

SQLite vs. SQL CE

time to read 1 min | 154 words

Those two seems to be the most common embedded databases in the .NET world. This is important to me, since I want to do testing against embedded database.

SQL CE can be used with SQL Management Studio, which is nice, but it has three major issues for me so far:

  • It doesn't support memory only operations. SQLite does, and it means a difference of 12 seconds vs. 40 seconds in running ~100 tests that hit the DB. This is important, especially kicking up everything seems to take about 10 seconds anyway (using Test Driven.Net)
  • It doesn't support paging (WTF!)
  • It doesn't support comparing to a sub query, so this is not legal:
    select * from account where 1 = (select allow from permissions)

Right now I am experimenting with just how much I can twist around to get everything to break.

I am on the third NH bug this week, and counting :-)


Steven Harman

So... are you advocating SQLite? At least until SQL CE can overcome those three issues?


And it doesn't support batching either.

Ayende Rahien

That actually doesn't bother me much, batching is most often useful for remote DBs, there is little sense to use it with embedded DB


I compared SQLite 3.3 with SQLCE-2.0 for a WinCE .NET project last year. I really wanted SQLite to work because you can only read 2.0 sdf files on WinCE. . For small DBs SQLite is great. Unfortunately, I ran into serious memory and performance problems with SQLite when the number of records got large (>10000). Ended up using SQLCE.

Ayende Rahien

That is surprising. Can you give more details about it?


Having used SQL CE for a while I can say without a shadow of a doubt that it strikes me as being a bit half baked. Why call it SQL Server Compact Edition and it lacks procedures, views and triggers? It supports only a subset of SQL, which can lead to nasty gotchas.

There's a whitepaper on Microsoft that tries to explain why a lack of features like views, triggers and procedures is a good thing. Talk about spin! I don't buy it one bit,

If VistaDB can do it, and so can Codegear Blackfish, I'd love to know why with all their experience and considerable headstart Microsoft have been unable to do it

Darius Damalakas

Another possibility is embedded Firebird, thouhg in-memory only databases are not supported. Might be a good choice.

Frans Bouma

CE desktop is a stupid design decision. Instead of using a trimmed down SQL express codebase, they opted for the mobile codebase. What's stupid is this:

  • it has a different SQL dialect So you can't switch between a CE desktop and a sqlserver 2005 db by just switching a connection string

  • it uses a different client. This alone is a showstopper.

I'd also opt for firebird embedded. The nice thing about firebird embedded is that you can switch from embedded to server by switching the connection string.

Jon Davis

SQL CE is a joke. It's like Microsoft went to their enterprise database folks and said, "Hey, let's scale this big thing down to fit into a mobile device!" Uh-uh, no, that's not the way to build a mobile database platform. To build a mobile database platform you scratch everything you know about prior database implementations, you re-discover your basic requirements (parse SQL, store data, do it fast), and you go to experts in mobile development and have them get to work, passing along any knowledge you can give them about lessons learned from the database world.. and let THEM decide how and if to implement the suggestions in accordance to the appropriateness of THEIR trade and THEIR experience with mobile software API consumers.

SQLite is not Oracle or mySQL shrunken to mini, and it's rediculous for Microsoft to expect people to take SQL Server 2000 shrunken to mini as a solid mobile platform.

SQL CE exists because a database runtime was needed for the Windows CE environment. People use it because it's there. Not because it's good.

Jon Davis

Heh. Scratch everything I said. I don't know SQL CE's background. Perhaps I had it backwards. Dunno.

I do know this: given everything I've heard, it sounds like SQLite is faster, and SQLite is capable and competent, the only question I would raise about SQLite is its deployability to the target environment.



I use SQLCE 3.5 with NHibernate at the moment. I am sometimes frustrated by it's lack of sub-select and trigger support.

I went for CE because I was interested in MSSynchronization services, and deployment via clickonce and in the general hope that MS would build in the same level of power and robustness that I have come to exect from the server edition.

Also, blog posts like this helped persuade me :)


Ayende Rahien

Yes, datetime handling are a PITA in SQLite. I really need to write a set of functions for it to make it nicely usable from NH

Stuart C

SqlCe does not support smalldatetime either. The best alternative I have found is VistaDB which is fully managed C# and can run in memory with 'full' SQL2005 TSQL support. There is currently a CodePlex project developing an nhibernate dialtect for it, in alpha stage right now.


I've been looking for ways to speed up unit testing against a database, and it really doesn't seem like there are any really good options out there.

SQL CE seemed like a good option but turned out to be a fairly bad product by all accounts.

VistaDB seems to be getting better and in theory is fairly SQL 2005 compatible, so you could develop against SQL 2005 while testing against VistaDB and without running into strange issues later, but would need additional licenses which could be OK if I intended to ever use it for anything other than unit testing - but at this point I don't.

Firebird seems neat and is fully supported by LLBLGen, which I like (Hi Frans!). It's also neat that (in theory) the only thing needed to switch from the embedded to server versions is a connection string change, but it doesn't support in memory databases to the best of my knowledge, so we're not really that much better off for unit testing performance purposes.

SQLite can do the in-memory thing, but it's not supposed by LLBLGen, and seems to have some other issues in general and also specifically with NHibernate, so I'm not sure what to think about it just yet.

If I primarily used NHibernate, I'd probably default to using SQLite at this point.

If VistaDB was literally a drop in replacement for SQL2005 (read: connection string change only), I'd probably purchase go that route, but it doesn't seem to be yet.

I wish there was an easier answer for this.


select * from account where 1 = (select allow from permissions)

Does that query make sense?

Shouldn't it be:

select * from account where 1 in (select allow from permissions)

or something like:

select * from account, permissions where account.userid = permissions.userid and allow = 1

Ayende Rahien


The real query is:

select * from account where 1 = (select allow from permissions limit 1)

I don't want the whole result set, I want the top one.

The second query affect the amount of data that the query return.

Comment preview

Comments have been closed on this topic.


No future posts left, oh my!


  1. Speaking (3):
    23 Sep 2015 - Build Stuff 2015 (Lithuania & Ukraine), Nov 18 - 24
  2. Production postmortem (11):
    22 Sep 2015 - The case of the Unicode Poo
  3. Technical observations from my wife (2):
    15 Sep 2015 - Disk speeds
  4. Find the bug (5):
    11 Sep 2015 - The concurrent memory buster
  5. Buffer allocation strategies (3):
    09 Sep 2015 - Bad usage patterns
View all series



Main feed Feed Stats
Comments feed   Comments Feed Stats