Elliott W (11/17/2009)
SQL isn't broken, but that isn't to say that it solves EVERY need. What I see most is tweaks to existing features.
I don't think tweaking or enhancing SQL is a viable way to proceed any more. Much is fundamentally wrong with SQL. For example the need to support duplicate rows, type coercian and three-value logic. If you fix those things then what you would be left with would not look much like SQL. Having gone that far, why even try to maintain the illusion that you are still using something called SQL? Especially given the more absurdly dated trappings of the SQL language, such as the byzantine complexities of the SELECT syntax.
I long since came to the conclusion that the only way forward for the long term is to break with SQL entirely. Perhaps we could keep SQL as an alternative data access language on top of our future RDBMSs for the sake of supporting legacy code, but for future purposes something better is needed.
Like what? The statement "we need to change" is useless unless there is some alternative.
Like an RDBMS based on Date and Darwen's "D" model. There are some open source versions already and some practical languages have been developed that go beyond their simple Tutorial D syntax. I'd welcome an industrial-grade implementation of an RDBMS based on D.
As long as you don't lose the set based operations otherwise all you get is RBAR and quite frankly THAT is a step backward.
Row-by-row processing is part of the wretched legacy of SQL. It just isn't an issue in a proper relational database model because everything is set-based.
Elliott W (11/17/2009)
General purpose integrity constraints with optimiser support
Please elaborate, this sounds interesting..
Support for declarative referential integrity and declarative constraints generally is very poor in SQL. One obvious consequence is that customers have to spend large sums on business rules engines, middle tier code and data quality tools to implement integrity rather than use the DBMS. For example try implementing a constraint that every order in the Order table must have at least one OrderItem. Or try to write a join dependency or inclusion dependency constraint in SQL. What's needed is the ability to write any query expression as a constraint and have the database engine not only enforce it properly but to optimise it and use it to enable query rewrites and therefore optimise other code.
Strong typing and type inheritence
Maybe..
Every other major programming language benefits from strong typing. No C++, Java or C# developer I know of would dream of doing without it. Yet SQL's type system is still in the stone age.
Self-optimising storage engine with more flexible physical storage options
The most important feature here is that we shouldn't have just one storage option per table. The idea that logical determines physical storage cripples database design. Why should I be forced to "denormalize" a table just to change the way that the data is stored on disc? That works against the whole notion of logical / physical independence. It is quite feasible to have a separate "mapping" tier for translating a single logical table or tables into any number of underlying physical storage structures.