I needed an embeddable database to handle some processing before

inserting the data into the final database. I worked with three

different embeddable databases, each had some pros and cons.

SQL Server Everywhere/Mobile edition

SQL Server Everywhere

is a free embeddable database from Microsoft, formally SQL CE. It’s

small, fast and offers almost full support for T-SQL. One thing that it

doesn’t support is square brackets for escaping names. Also, no support

for stored procedures, which wasn’t a concern for this project. The

main problem was that it will throw an exception if it’s running under

IIS. It’s obvious from the documentation that running under IIS is not a supported

environment, but there are some cases where it could provide a good

solution. I worked around this using app domains, which I’ll post about

later. It can be used with SQL Server replication which could be a hugely useful feature.

Being a MS product it has a fully supported .net provider and is supported on Mobile devices.

Embeddable Firebird

Firebird was

easy to setup and get going, but it does have one very annoying quirk.

It uppercases names by default and it doesn’t accept square brackets

for escaping names, but does accept single quotes. Also, it is case

sensitive so if you use them then you always have to use them unless

your names are uppercase. For example select * from ‘mytable’ is not the

same as select * from ‘MyTable’. And if you create a table with create

table mytable it will create a table named MYTABLE and selects will

need to select from MYTABLE not mytable. I don’t know if I explained

this clearly, but it is a weird quirk. This may be changed in version 2

which I don’t think has an embedded version yet, but could be a very good DB if this quirk was addressed.

The download section has the embeddable version and a .net provider.


Sqlite was also

very easy to get working and worked well for smaller data sets. Using

an index column or having a lot of data rows (10,000+) and things started

to really slow down. It could have been that I didn’t have something

setup correctly, but regardless it was performing really slowly. Setting up a auto

incrementing primary key was a bit tricky, the syntax is below. I don’t

see any advantage to this over SQL Server Everywhere, except that it’s

open source, it may also support stored procedures, but I’m not sure.

create table mytable (id integer primary key)…

The System.Data.Sqlite

project offers the Sqlite DB and a .net provider in one merged managed DLL, which makes

for a very clean distribution. Also, provides Mobile development


All in all, I liked SQL Server Everywhere the best, it has really good

performance a small footprint and a familiar syntax if you’re used to

SQL Server.</p>