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

And it doesn't support batching either.

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.

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

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

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.

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.

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.

Jon

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 :)

http://www.ayende.com/Blog/archive/2007/01/27/6916.aspx

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