What deficiencies, limitations or flaws do you feel exist with SQL and what would you change or add to it?

Question

Have you encountered deficiencies, limitations or flaws while using SQL?

I can think of several examples: Complicated queries/updates, SQL injection, etc. € Could you accomplish the same task easily with a different DML or programming language?

Can you provide me with case examples of problems you have encountered or cases where for example an SQL query required complex constructs vs. simple constructs in something else?

Can you suggest improvements to make SQL more powerful and less complicated? e.g. PSM, etc.

Which RDBMS/SQL product implementation do you feel is the most robust?

What capabilities from a non-SQL environment would you like to see implemented in SQL?..

Let's suppose SQL didn't exist, what would you use for manipulating data?..

Answer 1

SQL in general has some serious deficiencies as a database language. Just a few of the problems are:

• Duplicate rows (multi-set rather than set-based model)

• Nulls and three value logic add complexity, ambiguity and inconsistent results without adding any expressive power to the language

• SELECT statement syntax is much more verbose and complex than the relational algebra

• Lack of support for multiple assignment means that referential integrity support and support for constraints in general is severely limited

Two examples of more specific query problems that are hard in SQL:

• No simple equivalent for transitive closure in SQL. Selection from adjacency relation structures therefore requires the use of either procedural code, cursors or the non-intuitive and hard to optimise recursive query syntax.

• Lack of key inheritence means that SQL query interfaces invariably return simple two dimensional tables, which is a major drawback for decision support (OLAP) type of queries that are intrinsically n-dimensional.

Improvements? I don't believe there are any useful improvements that would make sense because fixing the above would change the language so radically that there wouldn't be much point in even pretending it was SQL any more. The best way forward I believe is to develop entirely new, truly relational languages. Date and Darwen's D model being the most obvious successor to SQL has already lead to a number of new implementations of relational languages.

Answer 2

From the viewpoint of a data manipulation language, SQL has a few weaknesses:

1. There is no standard SQL way to use regular expressions. Data constraints are often expressed in the form of regex's and having a standard regex language in SQL would be very useful. I know that SQLite enables you to put in your own regex engine, and I'm sure that other databases do it too, but a standard one would be nice.
2. SQL needs an imperative programming language within SQL, with language constructs such as variables and variable assignment, lexical closures, and iterators.
3. SQL needs a standard way to express user-defined functions that can be used in queries.

This is what I think would help it as a relational data manipulation language. For other types of data, SQL is not good enough.

Answer 3

Personally, I have a soft spot for CODASYL databases, where you walk datasets to access related data elements... perhaps because my first experience with databases was DECs DBMS. The ability to locate a parent record, then simply walk a set of children was simple to use; and the explicit relationships between datasets built into the underlying database structure is something that is sadly lacking in relational databases... and before anybody suggests it, foreign keys are a pale, one-dimensional shadow of that relationship.

The complexities of inner/outer/straight joins in SQL become an irrelevance, because the structure of the dataset maintains the relationships between different record sets (tables), allowing you to access only those dataset records that are related. Lookup an order and Walking the OrderLine dataset would return only the Order Lines that were part of that order, with no need to formulate a second query to retrieve that series of rows from the entire table of order lines for all orders.

The drawback was that almost all the design needed to be done up front... once the database structure was designed it was pretty much fixed, hard to change.... compared with RDBMS where it's much easier to add new tables at any time.... not particularly suited to a world of RAD/Agile/XP/Scrum.

Now if only somebody could come up with a database system where the structure was as flexible and easy to change as an RDBMS, with all the simplicity of data access of a CODASYL database.

OLAP databases are another great alternative to RDBMS, where the data can readily be structured as an n-dimensional cube, where a query allows you to "slice and dice" the cube, quickly extracting segments of the data. My first introduction to OLAP was Oracle Express, which (unfortunately) uses its own proprietary query language rather than the de facto standard MDX. However, not all data can readily by fitted to a "cube" structure, so it's only suitable for certain application types - although almost any data mining applications are well suited.

Answer 4

To add to the excellent answers by @dportas, for me of the great 'missed opportunities' in SQL is that the proposed temporal extensions, known as TSQL2, never made it into the SQL Standard. Consequently, temporal databases are extremely hard to get right even using Full SQL-92 e.g. multiple assignment would be a boon when, say, a sequenced deletion in a valid-state table requires four statements (an INSERTs, two UPDATEs, and a DELETE). When you consider that most vendors lack the support for SQL-92 features (e.g. SQL Server lack DEFERRABLE constraints, Oracle lacks aubqueries in CHECK constraints, etc) it is near impossible to achieve without the 'worst' kind of procedural code.

Answer 5

Fantastic answers, dportas :-)

While not as glamorous, something I wish for in the SQL standard, is the "INSERT OR UPDATE" constraint conflict resolution algorithm. MySQL and SQLite implemented this as extensions of the SQL standard.

This IMO should be intrinsic DB engine functionality, otherwise you tangle with things like:

IF (record_exists) THEN
    -- update record
ELSE
    -- insert new record

Which seems trivial, but it

• promotes bad habits like copy/pasting code block, which
• introduces bugs and
• just wastes your time, many times over.
• It also adds negative points to maintainability.

Two queries can be easily replaced by one:

"Insert OR Update Table (field = value) Where ID = @ID"

Feels so much more natural :-)