SQL Standard Confusion

Perhaps Yahweh was a bit harsh in his reaction to seeing the Tower of Babel. He looked with trepidation at the power that a single intelligible language, spoken by all, would give mankind. That tower was, alarmingly, being delivered on-time and to specification. 'Nothing that they propose to do will now be impossible for them. Come, let us go down and confuse their language there', he said and scattered them abroad.

This legend, one of the oldest that survive, will always resonate with developers just as it did for the Sumerians. Computer languages achieve their power by their consistency. SQL, sadly, has had the 'confusion' treatment. There is a reasonably consistent declarative syntax for SQL-92 queries but, beyond that, it is tough writing SQL-based applications that are interoperable.

Instead, you are obliged to stick with a particular brand of Relational Database Management System. You may experiment on the nursery slopes of a standard SQL, but the moment you need even just string concatenation or date handling, you see inconsistencies. OK, SQL is a declarative language, not a procedural language, but then even some of the declarative syntax is different. You'll even find implementations that are case-sensitive, completely contrary to the SQL standard. If the Sumerian Lord of Aratta (ca. 21st century BC) were alive today and developing SQL, he'd shake his head sadly and sagely wish "the whole universe, the well-guarded people—may they all query databases together in a single language."

There are several matters standing in the way of any move towards a shared syntax. One problem is the complexity of the SQL standard, now with JSON, time-intervals, pattern-matching and multi-dimensional arrays. No vendor is likely to add them all into their next release, especially if it requires a breaking change for existing users. Another is the fact that the Standards people were very late in deciding that SQL needed procedural extensions, and by then there were several incompatible and competing standards for procedural code for such database objects as functions and batches. This has resulted in database developers being faced with T-SQL, PL-SQL, PSQL, SQL-PSM, PL/pgSQL, SPL and ABAP, all of which established themselves before the SQL standard appeared. To make matters worse, the SQL standard tied down the syntax firmly without so much emphasis on the semantics. We can parse stuff without being clear what it means, and there are implementation aspects such as indexes which are very specific to a particular implementation.

You can't just delegate processes to middleware. The problem is that some processes, such as those within table-valued functions, can provide table-sources, so they are intrinsic to the data layer. The attempt, by ODBC, to provide a consistent syntax for every data source was noble, and worked remarkably well for simple usage, but has had to implement just a very simple subset of SQL. There are no obvious solutions to this problem; we remain boxed into whatever RDBMS we grew up with, suffering acute vendor lock-in.

Phil Factor

Tony Davis

Join the debate, and respond to the editorial on the forums

How to use Flyway Teams to run basic tests whenever it successfully executes a migration, checking that all the business processes supported by our database always produce the expected results.

Spotting anomalous behavior as soon as it happens is vital for preventing lost revenue, fines from regulators, and violation of customer privacy and trust due to security breaches.

Michael J Swart comes up with a function to convert the binary(10) LSN values from system procedures like sp_replincrementlsninto the format expected by sys.fn_dblog, for those times you need to delve deep into the transaction log.

The most common tempdb issue is running out of space, either regarding tempdb's overall size quota or the transaction log. Two factors to investigate are the service tier (pricing tier) that the database is configured with, and the type of workload that is executed against the database.

How to create a surrogate key with an identity column using SQL and Delta Lake

Parametrization was always a key aspect in ETL development to be able to move scripts, packages, jobs or whatever artifact you use into another environment. Unfortunately, Azure Synapse or Data Factory lack a configuration database, file or similar mechanism to help with this. One way to overcome this issue is by creating a parameters table in a database.

In the last post, we looked at creating the server application and corresponding Azure resources to secure it. If we try to test our application locally, we'll quickly run into issues - our API requires a valid JWT with appropriate roles.

Which databases are on Azure? How do I choose the right one? How do I migrate to an Azure database?

How to get started with Azure Functions, a serverless solution used for doing things in the back-end and the middle tier, like running scheduled tasks or processing data.

Need to search for a string inside a string? Never fear, PowerShell substring is here!

Part 2 of a 2-part series on web scraping and natural language processing (NLP), providing details on what NLP is at a high level, and an application of NLP called key word analysis (KWA).

Microsoft Purview is a family of data governance, risk, and compliance solutions that help organizations.

Data retention is a key component of your data governance programs. Policies must be defined early, shared via data literacy programs and technical controls built to automate the retention, protection and purging of data per policies.

A generic way of exporting, deleting and loading data, for database development work. It uses Flyway Teams, a PowerShell framework, JSON files for storage and a table manifest to define the correct order of dependency for each task. It should help a team maintain datasets between database versions, as well as to switch between the datasets required to support different types of testing.

Steve Jones bangs his head against case sensitivity problems when trying to learn more about graph databases.

How to configure SQL Server Always On Availability Groups between Windows and Linux SQL instances.

Having explained how you can read and execute the Second Stage Boot Loader of your own Operating System, Klaus Aschenbrenner moves on to how to remove the dependency from floppy disks and then switch our CPU into the x64 Long Mode, which is necessary to be able to execute our x64-based OS Kernel.

A quick look at a new DAX function to calculate working days, meaning days between 2 dates, ignoring weekends and (optionally) holidays.

Brent Ozar offers an example where estimated and actual plans don’t have the same shape, due to parallelism.

SQL Server Table Variables have a couple of good uses but, generally, there are so many downsides for most query patterns that they could all happen in quantum space and performance would still stink.

SQL Server can do a whole lot of things, aside from return the results of your queries. The thing is, the more of those you add into the mix the tougher it becomes to scale, and maintain reliable performance.

This DBCC command was originally created to "investigate performance issues related to the query optimizer“. How would a schema only copy of a database help with that? Well, it also copies the statistics and query store information.

Query Store is useful for forcing a beneficial plan but what if something changes that makes the forced plan impossible to use?

In this tutorial, we will look at how to load data to a SQL Server database using Python and an API. Then we will show how to build a simple Power BI report using this imported data.

Explaining the differences between these three components, when and where you use each, and how they work alongside other components of Power BI.

As Power BI matures, there is less and less to get excited about with a new release of Desktop, but a couple of things that caught Matt Allington's eye in the latest release.

The new Power BI Enhanced Refresh API lets you refresh individual tables in your dataset, override incremental refresh policies, control the amount of parallelism, and a lot more, but one problem remains: how can you schedule a dataset refresh using it?

How to troubleshoot language errors such as "Culture Name...is not valid or is not supported"when trying to open a PBIX file.

Mara Pereira describes how to share content outside your organization, such as for reporting purposes, through a Power BI Service.

Erik Darling offers some sound advice for any would-be SQL Server book authors out there.

Custom infix functions are one of my favorite features in R. This article is my love letter to them. But first, a quick recap.

How to install Active Directory Users and Computers and the basics of working with it so you can manage Active Directory.

There is often the need to concatenate data in Microsoft SQL Server in SQL queries or stored procedures to make one long string instead of having separate columns. In this SQL tutorial, we will show several ways that this can be done with T-SQL.

What does it do and how does it perform? Erik Darling investigates.