7. When optimizers no longer help
In order to break down database queries in the best possible way and to organize the individual processes, database providers and developers have developed optimizers. These can realize significant advantages – but their capabilities are limited when it comes to providing particularly extensive or complex answers.
Some database administrators only notice this when their application starts to scale. If the test data sets were sufficient during development but not in practice, this leads to problems.
8. Denormalization destroys everything
Developers often find themselves caught between two chairs: On the one hand, there are users who want better performance. On the other hand, there are the bean counters from accounting who are usually dismissive of the purchase of better (and therefore usually more expensive) hardware.
A common workaround is to denormalize tables. This ensures that complex JOINs or cross-table elements are eliminated. While this is not a bad technical solution per se, this approach also means that the cleverest parts of the SQL concept are thrown overboard. A database that amounts to an excessive .csv file most likely does not fall into the “smart” category.
9. Sudden database death
SQL has been constantly being expanded with new functions for decades. Some of them are pretty useful too. On the other hand, many of these functions are also rather “flanged” – which can not only lead to disadvantages, but also to the complete collapse of the database – keyword subqueries.
In most cases, problems in this area are only discovered when it is already too late. Then usually only a crisis-tested SQL silverback can help find the error by working through countless layers.
10. Vulnerable syntax
During the era in which SQL emerged, SQL was quite popular among human users. Today, many systems automatically stitch queries together, giving naive and malicious users a lot of power to cause harm. DBAs quickly learn to avoid certain keywords. But an occasional user might still want to SELECT GROUP use as a column. And then there are the wonderful standard solutions to words like SELECT To work around: MySQL uses backticks, PostgreSQL uses double quotes.
To make matters worse, clever attackers can exploit this vulnerability by injecting SQL commands into queries. A command like ; DROP TABLE users; DROP TABLE products; DROP TABLE orders;-- the SQL parser readily executes. After all, it was also written in a time when only humans asked questions.
11. Tables aren’t everything
A surprising number of data sets can be easily represented in tables. But a growing number no longer fits this shape so well. For example, social networks, hierarchical data and many scientific phenomena are modeled with graphs. These can be stored in tables, but anything beyond a simple query becomes complex.
Other data exists in two, three, or perhaps even multiple dimensions. However, tables only have one axis for the rows and one sub-axis for the various columns. Two-dimensional data such as latitude and longitude can be stored, but multi-dimensional calculations are difficult. New geographical expansions can compensate for this, but the paradigm remains limiting.
12. Standardization differences
SQL may be an ANSI/ISO standard. However, this does not mean that you can simply transfer it from one standards-compliant implementation to another. DBAs are very familiar with the many syntactic differences:
- MySQL used
CURDATE(), - Oracle
SYSDATEand - PostgreSQL
CURRENT_DATE.
In SQL Server you can use the +-Link operator. Others demand ||. And these syntactic incompatibilities are just the beginning. There are major philosophical differences between implementations of stored processes, triggers, and supported functions. Even the most basic data types have nuances in their areas when it comes to their precision.
13. There are better options
The best reason to abandon SQL: There are better, more concise, and more flexible alternatives. For example, GraphQL, which is often used in web applications to query exactly the right data combinations using a simple pattern. Hierarchical data is natively supported.
There are already several good alternative options available for searching NoSQL databases. Many of the key-value stores simply look for matching nodes. Some imitate the popular JSON standard, such as the MongoDB Query Language (MQL). Developers using document-centric solutions such as SOLR or Elastic Search can take advantage of complex similarity functions. These options support queries that are both more powerful and easier for humans to read and create. (fm)
This article originally appeared at our sister publication Infoworld.com.
