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
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.
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)…
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