[jchw]

One thing that has always agitated me about SQL is that although it's standardized, and the standard seems to encompass a shit-ton, in practice a lot of SQL engines don't really seem to have any meaningful interoperability for practical uses among the world's most popular database engines.

For example, OK, I realize auto-incrementing IDs are not the most important thing in the world, and arguably not even a good approach in many cases. But sometimes you want them, and helpfully almost every database engine I know of has some kind of support for this, even if the semantics may differ. It's a super basic thing to want a unique ID that roughly counts upward on a table. You might have specific needs about re-using numbers and whatnot, but the general idea is very simple.

However: in practice, there is not an excellent way to do it that I can see. The closest thing I could find is `GENERATED BY DEFAULT AS IDENTITY` which, well, works. However, none of SQLite3, MSSQL, nor MariaDB support this to my knowledge.

This is relentlessly annoying.

Is it the standards fault, or the implementations? I honestly can't say. However, I definitely find this annoying, since I was really hoping that by this time, we'd at least have a nice clean subset of standard SQL you could count on anywhere, for popular database engines. Unfortunately, it's not quite there yet, necessitating ugly hacks to this day.

I assume this new standard doesn't really change anything on this regard, since it's a desync with implementations that is a problem, and it does not seem the standards committee really cares too much about this kind of thing. (I could be wrong, though, as I am saying this based on feel and not evidence.)

[mdaniel]

> Is it the standards fault, or the implementations?

My mental model is that it's a mixture, but my life experience has been that a "standard" without a test harness or (at bare minimum) a reference implementation is just a bunch of navel gazing. For SQL specifically, that problem is even worse given the number of existing engines that move faster than the specification, so in the absence of leadership they just make stuff up

Natural language is also a catastrophically horrible specification mechanism, since your black/blue is my white/gold

[setr]

The way I see it is the fundamental problem is that SQL is not a proper composable language, and the standard never defines such a thing. So every feature added to an RDBMS is done so as an extension to the language, rather than an update to a standard library like any sane modern programming language. SQL as a language still operates with the mentality of COBOL -- if anything reusable is going to be provided, it will be provided by the RDBMS manufacturer. The user is only expected to produce highly business-specific logic for their own needs. (RDBMS's do often offer standard PL language support, but these typically hook into database internals, are intended for highly specific scenarios, cannot be transferred to any other RDBMS, and are generally meant for writing stored procs -- non-reusable functions)

As a result, for databases to compete on features, they must arbitrarily extend the SQL language standard; these modifications to the language then get backfilled into the standard, and runs headfirst into backwards compatibility, and suddenly no one agrees on really what the feature should precisely be so they it becomes an optional part of the standard, which really just means that it isn't standardized.

In any sane language, you wouldn't need different databases to add specific support for GENERATED BY DEFAULT AS IDENTITY, and especially not for 15 different syntax's used in 15 different databases to specify kinda sorta not really the same thing -- it would simply be a function, one you could write yourself, or provided by the standard library. It wouldn't be up to the RDBMS to offer support beyond actual language features -- it'd just be up to you to update your libraries.

[galaxyLogic]

> SQL is not a proper composable language,

Very good point. Non-composable mean non-scalable (language).

I wonder if it would be possible to define a very small robust standard subset of SQL with a standard for extending it. But that would take away the customer-lock-in which is what all big DB-vendors desire.

[dmux]

SQLite does support auto-incrementing: https://www.sqlite.org/autoinc.html. Is your gripe with the lack of "GENERATED BY DEFAULT AS IDENTITY" syntax specifically?

[jchw]

Yes, sorry. My gripe is that we don't have a single general way to say "I don't really care about the specifics, I just want a number that goes roughly upward" for all database engines. SQLite is interesting in that it supports at least two distinct sets of semantics for auto-incrementing rows, but neither of them are the standard syntax...

[richardwhiuk]

The gripe is that there's not a standard say of defining this in SQL.

SQLite SQL != MySQL SQL != ISO SQL