In 1974, Abba won the Eurovision song contest with “Waterloo.” That same year saw the birth of Structured Query Language (SQL). Created as a standardized language for relational database management, it simplified enormously the development of complex business applications. For example, you could fetch many data records in one statement without specifying how to reach that data. Having spent 19 of the past 20 years working with relational databases, and much of the past year writing code to access a hierarchical database, I can appreciate the ease that SQL provides developers. The equivalent of a two- or three-line SQL query with joins between tables can become 20-30 lines of code in a hierarchical database. As languages go, SQL is a bit of a mishmash of declarative and procedural programming. You can declare databases and tables and then modify them in code, as well as insert/update data. Although there are various standards, various database creators have added their own extensions, with Microsoft and Oracle being amongst the worst offenders (to put it mildly). If you've ever moved from MS Access to SQL Server, there are subtle (and not-so-subtle) changes; for instance, use of " in Access become' in SQL Server. Data isn't exactly siloed, but moving non-trivial quantities of data between different makes of database can require quite an effort. Some developers viewed the NoSQL movement, which began in 2009, as possibly replacing SQL. But it really just solved a different problem: How to store multiple records rapidly. Relational databases have ACID (atomicity, consistency, isolation and durability), which add a processing overhead that NoSQL avoids. The price of NoSQL’s speed is consistency, which is replaced with eventual consistency (if there are no new updates to the object after the period of possible inconsistency has finished, all accesses will return the last updated value). SQL, having weathered the NoSQL storm, continues to be revised, with the last amendment (the 8th) in 2016. It took 12 years for the language to become a standard (in 1986); the first amendment was in 1989. Most development that I’ve done since 1998 has been against SQL-89 and SQL-92 databases, despite the newer versions since. Things change slowly in the world of software development!

SQL and Relational Databases

You can't have a discussion about SQL without referring to the databases running it. What versions of SQL do the major databases support? I'm looking at Oracle, SQL Server, IBM DB2, PostgreSQL, and MySQL/MariaDB. SQLite is included in every Android and iOS device, making it the most deployed database in the world. Oracle, although not quite the first commercial relational database, is the most popular now and considered the one to beat. According to Db-Engines, it is still the most popular relational database, followed closely by MySQL, then SQL Server. Oracle supports SQL:2011 and row pattern matching from 2016. Oracle's other database, MySQL, was acquired along with Sun microsystems in 2008. Some thought MySQL might not survive the transition, including its main author (Monty Widenius, who created the MariaDB fork). MySQL supports some of SQL-2011. Not far behind Oracle/MySQL is Microsoft SQL Server, which not so long ago appeared on Linux. This transfer was a technically challenging task, because SQL Server has traditionally made use of Windows libraries for threading/scheduling, synchronization, and IO (disk and network). The team accomplished this transfer by adding a Platform Abstraction Layer. It comes with the same licensing as the Windows and developer/Express editions, as well as standard and Enterprise. IBM’s Db2 isn’t one I’ve personally utilized; but according to their website, it supports many of the SQL:2016 standards. There are a number of different versions, including two express (i.e. no charge) community editions. Db2 is another SQL database that can store JSON documents (like No-SQL) and has done so for the last five years. Just to be clear, DB2 is not the same as Db2. IBM Db2 is a successor (introduced in 2017) to DB2, which has been around for three decades. Coming in at number four in the Db-Engines ranking is PostgreSQL. This has always been the most technically advanced open-source RDBMS. PostgreSQL 11, the newest version, supports most features of SQL:2011. If you want a sophisticated RDBMS, PostgreSQL is arguably the best, certainly for the price! The Express versions of commercial databases are generally restricted in the size of database they can handle; PostgreSQL is not. I like SQLite. It’s small, fast, and a handy thing to build into desktop software. It’s also less than 700KB in size. It supports most of SQL-92 and that’s it—alas.

Conclusions

According to db-engines, the fifth-ranked database is now MongoDB. This shows that, even after nine years of NoSQL databases, relational databases nonetheless remain top dog. I attribute some of this to inertia, and a bit to some relational databases adding in NoSQL abilities. PostgreSQL and MySQL both support JSON. It's far easier to keep your existing relational database if it has NoSQL support.