Well I would actually advocate the question/title of this topic is misplaced. This is really the question I would have asked probably 4 months ago, and in all likelihood dismissed SQLite after hearing the answers! So bear with me, its worth the read! The key question that really should be asked is not how it stacks up, but what are its strengths and in what circumstances should I use it. In essence what problems is SQLite designed to solve rather than academic bullet point comparisons.

However back to the question – How does it stack up?

As you can see SQL Server definitely has the most features, in fact as far as features go its a slam dunk. However often feature count just isn’t everything. The major drawback for SQL Server is that it requires a Server process to run, and that then means maintenance, probably a DBA and all of a sudden things have got complicated (and expensive)! So we come back to the question “What are we looking to do with the database?”. If the answer is heavy duty processing that requires stored procedures, cubes and large amount of concurrent remote access by users – SQL Server is definitely your answer. However I have to think that you are reading this because SQL Server is too heavy weight for your needs. So on to the other candidates.

SQL CE is a Microsoft database that started in the mobile space and moved up to the PC platform. Overall is has reasonable features but is known to be slower and heavier than SQLite. It definitely has some strengths in the SQL Server ==> SQL CE replication, but outside of that its largely even or less featured that SQLite. If you are looking for replication definitely checkout SQL CE, but I would also say the Microsoft Synch framework is evolving and we may see a native SQLite provider (or determine that the ADO.NET is sufficient) in this area.

SQLite is a lightweight add-on to C# (System.Data.SQLite.DLL ~ 850Kb). You may note this is heavier than the often referenced SQLite runtimes take “200Kb”, but in this 850Kb (457Kb Zipped) is the SQL DLL, ADO.NET SQLite provider and the magic glue to bring these two worlds together. If you are using .Net you can assume you have to assume your package size will go up by 457Kb, and the runtime size on disk will be ~850Kb. Performance of SQLite ranks it above SQL CE for insert tests, and it has a lower file size – and the database can be assessed on a massive number of operating systems!

Before you jump whole heartedly on the SQLite bandwagon, it is only fair to also point out SQLite has some quirks (SQLite folks call these ‘features’ – I’m not going to argue, but its approach is different in a couple of cases). I’m going to cover these in the next blog entry “Part 5″. These are things that you definitely need to be aware of rather than discover!

The key question that the article should be asking is “What type of application features benefits from SQLite?”. The best way I can describe SQLite is that is is an “Application Data Store” that you then have ANSI-92 SQL access to. This removes the needs of rolling your own indexing into files, creating convoluted file system ‘databases’ etc. If you can wrap your head around how you can use SQLite to your advantage I think you will be pleasantly surprised.

Related Links:

• SQLite for C# – Part 1 – Am I allowed to use it?
• SQLite for C# – Part 2 – How do I setup a SQLite DB (without coding)
• SQLite for C# – Part 3 – My first C# app using SQLite aka Hello World
• SQLite for C# – Part 4 – So how does SQLite stack up against other DB’s?
• SQLite for C# – Part 5 – SQLite ‘features’, or ‘quirks’
• SQLite for C# – Part 6 – SQLite Connection String Definitions
• SQLite for C# – Part 7 – Building SQLite.Net from source
• SQLite for C# – Part 8 – Loading CSV/Pipe into SQLite via command line