Although I dearly love using SQL Server, I wouldn't use it in every circumstance; there are times, for example, when just isn't necessary to use a Server-based RDBMS for a data-driven application. Although a lot of developers are increasingly using NoSQL solutions to provide data storage for 'personal' applications, I really don't think that this is always the best choice. SQLite is my preference. Why? It is arguably the most popular and well-tried-and-tested database ever. It is probably in your phone, and used by your browser. Your iTunes will use it. Most single-user applications that need to handle data will use SQLite because it is so reliable and easy to install.

It is specifically designed as a zero-configuration, embedded, relational database with full ACID compliance, and a good simple dialect of SQL92. The SQLite library accesses its storage files directly, using a single library, written in C, which contains the entire database system. Creating a SQLite database instance is as easy as opening a simple cross-platform file that contains the entire database instance. It requires no administration.

I can pass tables, and their data, easily between SQL Server and SQLite, using either ODBC, a NET library or a nice, simple command-line file that is reminiscent of SQLCMD. Of course, there is some compromise since there aren't nearly so many data types in SQLite and, like most NoSQL databases, the data is represented in storage as a string. There are many tools around for working on SQLite databases if you're not a programmer by inclination.

There are reasons for using SQL Server instead once your application grows, because the performance and simplicity comes at a price. Although SQLite will happily handle data sizes over a gigabyte, it is limited to a single file. Where you need concurrency, ACID compliance isn't really enough because it relies on file locks, so write transactions are effectively serialized. There is no access control beyond that provided by the operating system. There is no way of using SQLite in a client-server environment. There is also no way of replicating the data or providing redundancy.

Because a SQLite database is a single file, it can be used like XML. You can send it as an attachment, query it, export and import data, all using the SQL language you already know. It is therefore a great tool for the DBA. I use it for storing all the test data for Continuous Integration processes, but it has value wherever you would otherwise have to use XML or, eek, JSON. If you are within the SQL and relational mindset, there is no good technical reason to have to change, since SQLite takes care of all the small stuff and the new breed of NewSQL takes care of the jelly-masses of unstructured data.

Phil Factor