Embedded Databases

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

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 support.

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.