ANSI SQL Is Dead
-
ANSI SQL Is Dead
OK maybe it’s an exaggeration. For now. ANSI isn’t completely dead, but it seems to be fast going the way of Microsoft Bob. To quote Peter Griffin, “You know what really grinds my gears?” Answer: When some SQL guru posts lowest-common-denominator SQL code solutions to a Microsoft SQL Server-specific forum. It really drives home just how horrible a “portable” solution can be. Trying to write non-platform-specific SQL means you can’t take advantage of SQL Server-specific features that improve performance. In fact you end up writing non-performant kludges to manually recreate those features yourself. ANSI compatible lowest-common-denominator is effectively the same as slowest-common-denominator. But just how bad can it get?
Consider life without SQL Server user-defined functions and stored procedures. We can also eliminate dozens of other additional T-SQL features that we all depend on to maximize throughput. To top it all off, “portable” solutions are often not “portable” enough to actually run on MS SQL Server. Pick a version, any version, and try to use the “||” operator in a query. But some still cling to the hope that ANSI is king. Look at the facts: ANSI SQL-92 was based on a compliance-level model. To be in compliance with the standard your DBMS had to implement a minimum specific subset of features. SQL Server 2000 was SQL-92 “entry level” compliant. ANSI SQL-99 is based on the “a la carte” model. Basically your DBMS has to be SQL-92 entry level compliant, and the rest of the standard you get to pick and choose what you want to implement. As far as I can tell, this means that a SQL-92 “entry level” compliant DBMS can simply toss the ROW_NUMBER() function into the mix and “voila!” Your marketing department can now advertise your product as “ANSI SQL-99 Compliant”! Boy that was easy.
The SQL-99 standard is well on its way to fragmenting the SQL world even worse than it already is and destroying any hopes of portability on a scale larger than any single vendor has been able to do over the decades. ANSI SQL still has a place, but it should not be misused to force plain vanilla, non-performant and kludgy code on developers in utterly ridiculous attempts to skirt vendor-specific performance-enhancing features.
Michael Coles
-
Bravo! I don't know how many times I've said the same thing. Most recently I've been pulling my hair out over Hibernate which forces you to use "ANSI SQL" as if that dialect actually exists somewhere in the real world. It just sucks! No CTEs, no variables (at least not that SQL Server can use), no stored procedures (again, not the kind that exist in SQL Server), no built-in functions, and no derived tables. It's like being in SQL hell.
The idea of portability is a joke, it doesn't exist either (Java included). Forcing everything down to the lowest common denominator is a loosers game. Play to your strengths or go home.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Nice editorial. It's just too accurate.
I've never seen an app ported from platform to platform without major rewrites. Those rewrites have occurred, not simply because of the platform shift, but because the business needs have changed or we're adding more power or more flexibility or because we're taking advantage of functionality offered by the new platform and frequently, all of the above. I suppose in the world of software vendors, it might be a nice to have feature saying your code will run on Oracle, SQL Server, MySQL or Sybase, but most business apps need to run as fast as possible on the platform of choice. Giving your code a lobotomy because someday, somebody, might decide to switch platforms is a pretty foolish choice from the get-go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I find it impossible to disagree with today's editorial, but I wish that the major vendors would sit down and agree upon a real-world cross-platform lowest common denominator. It might be handy to at least see what they have in common, and having them proclaim a specific compatibility level to ANSI doesn't do anything to achieve that.
-
I'm the database designer for a major higher education ERP application (in the hundreds of customers) that rides on top of both SQL Server and Oracle. This is on a schema with over 800+ tables. It can be done, however the testing process is about a factor of 1.3 or 1.4 times as difficult and you have to have sharp developers (which we have).
Not wanting to start any religious wars here, but I find that Oracle has a greatly more functional programming language in PLSQL than TSQL ever will be. I've started playing with SQL CLR in 2005 and this brings SQL Server forward light years. TSQL has kept me from writing stored procedures that I would otherwise do in PLSQL. We essentially wrote a few functions (LEFT, RIGHT, SUBSTRING, ISNULL, GETDATE, etc..) in PLSQL that we wanted to use from TSQL and we are pretty functional.
-
In my 15 + years of programming, my ones and zeros in my code are still ones and zeros. When that changes I might have to worry. There has not been any improvement to higher level programming languages since the advent of cobol or FORTRAN. Nothing truly portable is ever good. I'll use the stupid example of emulating an "Intellivision" game console on a PC, or cutting glass with a stapler. (No, you cant have my stapler)
Personally I'm flexible enough and ready to pick up any new tool or technique if it lets me get to play with my stapler more.
I like the 2k5, with the CLR, and mostly letting me switch back to 2k, if its easier. It's nice to be able to recreate most of my entire app and convert from language to language simply. Why have code 'ready' to be converted, when preconverting it makes more sense, should it be simple as in .NET to do so, and since it will probably run much faster.
An old teacher of me told me words I live by "Presorted data, will almost always be better,faster, stronger, than unsorted even if the sort is an arbitrary one".
Nothing grinds my gears more than working on a app to find it not responsive. It bothers me more than most end users. With the .NET it's easy to find a new tool and if its not in the scope of ANSI version whatever, then so be it.
Cheers,
Edward W. Stanley.
PS. Where's Steve?
-
Other's have said it, but I want to reiterate that the standards immensely help people writing software that they hope to sell. If you are living in a secluded, corporate 'Microsoft shop', you can code using all the MS proprietary syntax you want. However, if you are a solutions vendor, and hoping to sell your product to non-MS folks, you'd better stick to the lowest common denominator. But like Mike said, it's not always optimal to kludge things together to keep compatibility, but that's why we have if/else statements.
-
While I agree with everything that was stated, I'd also like to add that there are SOME pieces of the ANSI SQL that DO make life easier for the developer... in particular the JOIN syntax can result in clearer and cleaner SQL queries. That, at least, has nothing to do with the portability chimera.
-
I guess I'm the lone dissenter here! I started working with SQL when IBM's DB/2 came out for the S/370 around 1983. Since then, I've worked on I don't know how many DBMSs -- hierarchical, relational, network, you name it. Currently, I work with SQLServer 2005 and MySQL 5.0. There are so many syntactical differences between these two implementations of SQL (string handling is but one small example) that I can get myself confused in a heartbeat. And being about twice the age of the average programmer at my shop, the ol' memory gets confused pretty easily these days as it is.
So I'll come out, in a reserved kind of way, for standardisation in general, and for ANSI SQL in particular. Standardisation is a good thing -- so long as the standard itself assists the developer in effectively and rapidly accomplishing the tasks at hand. And if the standard could use a few improvements -- well, write to ANSI, or better yet, try to join the appropriate ANSI committee.
In the meantime, I guess I'l keep dusting off the SQLServer and MySQL manuals.
Cheers.
-
"Where's Steve?"
Steve is "in the house". He was kind enough to let me rant on today's editorial page
As for you guys' comments, I agree totally. There's an old saying: "A camel is a horse designed by committee." And while a camel is a good choice to get you across the desert at a nice slow gait, you'll never see a camel win the Kentucky Derby. The problem to me is the disconnect between the hypothetical, theoretical, academic world of ANSI SQL and the real world we live in where the boss is breathing down your neck to get it done faster, better, cheaper.
Too many "academic" SQL Gurus see ANSI SQL as encompassing the complete set of tools that a vendor should implement. The rest of us in the real world tend to see it as the bare minimum. And of course, as of SQL-99, the vendors no longer have a "bare minimum" to even shoot for. If the accountants decide that the time spent implementing a statement, operator or keyword does not have a high enough ROI, why do it? The marketing department can claim "compliance" with any functionality they choose to implement. In fact, if you ask Microsoft if they are SQSL-99 compliant (I have asked managers and developers on about a half dozen occasions), the answers vary wildly, including:
- "no"
- "I guess so"
- "yes"
- "sort of"
- and my personal favorite: "we implemented what we thought was important from the standard and ignored the rest"
SQL-99 of course gives vendors a license to "ignore the rest." We can expect the tools we get in SQL Server and other database platforms in the future to be defined not so much by an overarching standard or DBA/developer demand, but rather by a bunch of accountants plugging away ROI calculations into an Excel spreadsheet in the basement.
Anyway, just my $0.02. Thanks!
-
I completely disagree with todays editorial. You use the standards as much as you can and strive for portability. Use specific tools of a technology only when absolutely necessary. I worked on too many conversion and refactoring projects and the thing that makes most of them painful is a "guru" in a closed microsoft development "box" doing very specific MS only things instead of just good design. Yes, in situations you have to use non-standard tools/functions but it should not be the case that you use them as much as possible.
Java, the CLR, XML, SOAP, ANSI SQL, etc. are all moves toward portability and making those external technologies dependant on some esoteric Sql Server calls is crazy unless there is no other way.
-
Standards always lag behind current developments. They have to.
I find it hard to get enthusiastic about SQL-92 when we are in the last half of 2006.
If you are a solutions vendor then you probably have to stick with the standard to make your apps portable but unless you are in a large outfit choosing an RDBMS is a one off process.
What is the point of making something portable when it doesn't need to be? At what point would you decide to ditch SQL Server to move to ORACLE, MySQL or SyBASE?
-
Portability is a mixed goal.
It eventually worked with c, and later c++ because the languages was designed to be a fully functional (pun?) method or expression, even with structured allowances for different word widths, etc. Libraries could manage all the platform specific stuff.
SQL depends on a sophisticated runtime engine (Microsoft, Oracle, IBM have each done fabulous things with these) to do things that are simply not in the language itself. But conflicts, business or philosophical, have prevented anything comparable to the C runtime library from ever being established.
...
-- FORTRAN manual for Xerox Computers --
-
I agree. You write SQL statements in order to solve problems. I'll use the ANSI flavor if it is equivalent, both from a query result set as well as a performance point of view. I've used ANSI Joins because it can more clearly and cleanly describe the type of join without a performance penalty.
I don't think that it's probable that we will ever have the same functionality from ANSI SQL that "Native" SQL has. That's life.
-
Great editorial and thanks for the break, Mike
I agree and while I like standards, building portable solutions seems silly to me. Most of the larger software companies need someone that's an expert in each platform anyway to be sure their product is tuned. So why not just rewrite the SQL in platform specific language to run quickly. After all, If venture to guess the SQL code is much less in terms of size than any front end code.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply