Panasonic Youth rob sanheim writes about software, business, ruby, music, stuff and things

Sql standards - how exactly did this get so f’ed up?

Lets say you want to get the first n number of rows from a result set, to do pagination or because you don’t care about the rest of the rows or whatever. You would think that there would be some relatively standard way defined in sql, and then the database vendors would all try to follow that. It would be reasonable if vendors supported the standard and then provided their own super cool optimizied way.

If you take a look at this comparison page, however, you’ll see that only 2 of the 5 vendors listed support Rownumber(), and then each vendor seems to go out of their way to do it completely different. Use “top n” in the select clause, “fetch first” after the order by, use “limit n”. Its not like you can just tweak a clause at the end of the statement to try and be compatibile, for some of the differences you have to rewrite the whole statement. And this page only lists five vendors, I’m sure if you included some of the smaller vendors it would just get worse.

This is ridiculous. This cannot be that difficult. Why can’t the vendors just say “lets work towards ANSI 200x for future versions, because the current situation sucks and we are screwing the users”. I understand they all want to push their own “optimized” features, but why not do that over a minimal baseline that is common amongst everyone?

As a side note, are there any official pages for what the ANSI SQL standard actually is? Does ANSI SQL even matter anymore?

It’s called vendor lock-in.

The comparison page is not up-to-date regarding MSSQL. In MSSQL 2005, Microsoft seems to have added the needed “window functions” (I haven’t personally verified it yet, though), so the “big three” are actually following specs on this issue.

maik: Right, I understand the short sighted reason that vendors might do it. It seems all it does is give developers more hassle and pain. If you really forsee the need to migrate, you’ll put in an abstraction layer that handles this stuff anyways. I’m just saying it shouldn’t be that hard.

I suppose I’m being too idealistic.

Troels: Its good to hear there is some progress on it, at least for something as simple as row limits.

Any world where i’m stucking writing SQL is a world I don’t want to be a part of.

Long live ORM, even if it’s at the expense of performance.

The ANSI/ISO SQL committee creates standards primarily based on existing implementations. When existing implementations differ in their approach to a problem, then the committee choose the “best” one, or invents their own “best-of-all-worlds” solution. For example, ” IS NOT NULL didn’t hold in Oracle at the time it was standardized by the SQL committee, but it held in other dialects. ANSi/ISO decided “Oracle is wrong, everybody else is right.” But, Oracle couldn’t just flip a switch and leave its customers with problem applications to become SQL-compliant.

Also, the SQL standard is a very, very large specification. It takes tons of effort to implement and verify conformance to even small parts of the standard, regardless of any legacy issues. And, standards conformance has to be balanaced with innovating features that attract new customers.

If Microsoft had decided that they were going to drop everything to conform to ISO SQL Entry-level, by the time they were done they wouldn’t have C# integration, XML integration, analytic services, or many other features for customers make decisions on.

hope to checkthis out soon……..