What relational database innovations have there been in the last 10 years

Question

The SQL implementation of relational databases has been around in their current form for something like 25 years (since System R and Ingres). Even the main (loosely adhered to) standard is ANSI-92 (although there were later updates) is a good 15 years old.

What innovations can you think of with SQL based databases in the last ten years or so. I am specifically excluding OLAP, Columnar and other non-relational (or at least non SQL) innovations. I also want to exclude 'application server' type features and bundling (like reporting tools)

Although the basic approach has remained fairly static, I can think of:

• Availability
• Ability to handle larger sets of data
• Ease of maintenance and configuration
• Support for more advanced data types (blob, xml, unicode etc)

Any others that you can think of?

Apopemptic

Answer 1

• Hash joins
• Cost-based optimizers (pretty much turned query-writing on its head)
• Partitioning (enables much better VLDB management)
• Parallel (multi-threaded) query processing
• Clustering (not just availability but scalability too)
• More flexibility in SQL as well as easier integration of SQL with 3GL languages
• Better diagnostics capabilities

Phebe

I remember a relational DBMS with a good cost based optimizer in the 1980s. – Genna

Answer 2

Analytic functions like RANK

Treillage

Answer 3

I'm not sure if you want to include even vendor-specific innovations (and nor am I entirely certain that other database engines can't already do this), but SQL Server 2005 adds recursive transact-sql queries to their language. I find them amazingly useful for iterating over hierarchical data. I believe 2008 adds some new functionality related to hierarchical data, but I haven't looked that closely.

Exterminate

Vendor specific is definitely important - after all that is where the innovation is since the ANSI92 departure – Apopemptic

Common Table Expressions will bring recursive query support to PostgreSQL in 8.4 (not out yet). Hooray recursion! wiki.postgresql.org/wiki/CTEReadme – Yoshida

Answer 4

SELECT (invoiceprice * detailweight) / SUM(weight) OVER(PARITTION BY invoice) as weighted, * 
FROM tblInvoiceDetails

Windowed functions are awesome for doing things like weighted averages, and other things that previously required CURSORS.

Cluff

Answer 5

Well one could possibly suggest that a lack of movement for 15 years is not just a sign of lack of innovation, but a sign that databases are almost perfect! Many people try to do things in code that are better done in databases that have been refined since the 1960's to run as fast and as efficiently as possible.

Foreground

Answer 6

I would say the last ten years (1998-2008) have seen open source RDBMS products become viable in mainstream deployments. Most Fortune 500 companies now use MySQL or PostgreSQL or another open source RDBMS somewhere in their organization, even if they also use one of the commercial, closed-source RDBMS brands.

This isn't a technical advancement, but it's noteworthy nevertheless because the availability of a stable, open-source RDBMS engine enables many other innovative projects.

I realize that both MySQL and PostgreSQL were available as early as 1995, but I would argue that they weren't mainstream for several years after that.

Xever

Answer 7

Along with your list of more advanced data types (blob, xml, unicode etc) you should include spatial types.

The PostGIS extension for PostgreSQL came out in 2001, but now all the major vendors have implemented spatial objects and spatial SQL.

Along with the rise of Google Maps, Bing Maps, and OpenLayers the ability to display geospatial data and run spatial queries without middleware has had a huge effect on the web and data analysis.

Pyrimidine

Answer 8

I think most of the progress has been in the realm of performance - query profilers and clusters.

Seleucia

Answer 9

I think that the area of biggest innovation has probably been in data replication - for availability and reliability. Most of the other areas are more incremental. By specifying a decade, you omit the ORDBMS stuff - extensibility; that appeared in 1997.

Estoppel