Discussion on:

Is SQL a standard anymore?

PL/SQL and Transact-SQL are not SQL variants - they are 3GL programming languages that are (typically) used to wrap SQL. Criticizing them for including non-SQL92 elements is as pointless as saying that about COBOL and FORTRAN.

This is the second straight article in this forum by people who appear to know nothing at all about DBMSes - what's going on here?

I think they are just looking to fill space and generate content. Why have standards? What a stupid question. Without standards, software development would be nearly impossible.

The section titled 'why have standards' is an explanation of why they are necessary, not a call to disband them.

I'm not criticizing databases for supporting addtional functionality - I'm making a very one-sided point to spark discussion. The reason for listing Oracle extentions was to illustrate that even though there is a standard, you cannot easily port data from one implementation to the next.

Also SQL variants do not "wrap" SQL - PL/SQL and Transact-SQL are implementations. SQL is not a code base, it's a standard. If it were truly 'wrapped', the syntax in the specification would be the same acrossthe board. it is not.

"PL/SQL and Transact-SQL are not SQL variants..." That's a quibble, the point that the both query languages include proprietary extensions to "standard SQL" is still valid. Try porting an app from Oracle to SQL Server or vice-versa and you'll get the gist of it.

Just out of curiosity, which other article from someone who appears "to know nothing at all about DBMSes" are you referring to?

Oops, now *I'm* being inaccurate. So as to not add fire to the flame, I'll just state that it was a second article, not a second author.

> Just out of curiosity, which other article from someone who appears "to know nothing at all about DBMSes" are you referring to?

i would venture to jest that as long as the gov't is putting together a list of xml standards, a sql standard might not be necessary. you could port the data into the sql db of your strength and hack from there.

and i might venture to say that the farther and farther we get into the future and rdms technology changes beyond the reach of sql92 (or 99), a group, some group, will step up to the plate to set a standard. even a conglomerate of companies to set those standards. it has happened in different areas, as wild of a notion that it may sound like.

Why is the same person, who wrote "oh, my--MySQL" writing a column on SQL standards? Not only that, but she doesn’t understand that procedural languages have NO relation to SQL. Oracle supports Java for God’s sake. First off, someone who is praising MySQL for its wonderful database abilities shouldn't be discussing SQL standards... Especially as their beloved MySQL doesn’t support enough to run the TPC/C. I must agree with the author who stated it is like comparing FORTRANto COBOL. Why these people are published is beyond me. Disseminating incorrect information to users of similar or lesser experience to make you appear to be a better DBA (used lightly) is wrong. Please, please, please, do NOT write any more columns about databases until you understand more than a single one from only a user point-of-view, or, until you can read the standards documents. Besides, there are database projects out there that are addressing this article's concern (http://www.nightstarcorporation.com/?op=products), which I would say aren’t mentioned due to the author’s apparent lack of research.

I feel that my purposely inflamatory article has hit a nerve with a few people. Good. I'm only sorry that you missed the point of the article - I was hoping to bring out an intelligent discussion about standards. I said it in a comment above, and I'll say it again - I was not criticizing database manufacturers. I was illustrating the lack of effectiveness of the SQL standard.

Since you were so adament about slander in your comment, I feel like I need to point out to the readers that I have extensive database experience in a variety of capacities, including integration, migration, administration, design, and yes - use. Also, my professional work history includes (in order of most to least): Oracle, SQL Server, Informix, MySQL, Sybase, DB2. My personal use and deployment includes several others.

I'd also like to point out that I do extensive research for every article, and do not knowingly include misinformation in my work. As I said before, this article was purposely one-sided inorder to make a point. I am not questioning the need for a standard. I am questioning whether an effective one exists.

First of all, relational database management system is abbreviated RDBMS, not RDMS. Webster's and acronym finder do not define RDMS as you have, nor do any of my co-workers or my contacts at Oracle or Sybase. Perhaps you should petition them to beadded, as to make yourself correct. Now, to the guts of the article. I would normally go through the entire article picking apart your words and correcting them. However, I do not wish to continue with this, "slander". Therefore, all I ask is that you do not act so authoritative when others who spend the time to read and post to these articles may know more. I develop commercial database systems and have written many from scratch. Besides you, I have seen many other users trying to coercepeople into bad database combinations by not giving them enough factual information as to the limitations of the solutions you offer. You may have administered Oracle, SQL Server, and the like... that's great. Your items for discussion, for the most part, are good. If you would like to co-author some of these articles, I would be happy to assist. Sorry for the flame.

There is never a need to apologize for expressing your opinion or sharing your experience in the discussion center. The discussion boards are here so that readers can benefit from the community's knowledge, and I appreciate the fact that you use them.

Like you, I merely take the time to respond and participate in discussions. I don't feel that providing a response is an act of coersion, or an attempt to promote myself as an authority, I just enjoy being involved.

For the record, RDBMS is the official acronym for Relational Database Management System - however RDMS is commonly used and well-documented.

Agreed - RDMS is a common enough way to refer to RDBMS. I hear and read of both in the different circles I move in. RDBMS is, I find, more common.

Agreed - RDMS is a common enough way to refer to RDBMS. I hear and read of both in the different circles I move in. RDBMS is, I find, more common.

With the growing use of the internet to provide an interface to databases, a need for a definite guarded standard is necessary to keep everyone on the same page. Beside, it's also job security. If there is no standard, what's to stop a company or series of companies from changing software and firing people so they can hire people who know something else.

In my work environment, we typically provide custom solutions for clients. It is extremely rare that we ever have need to port between different databases while at the same time maintaining a schema. Usually our porting will be from a non-database(i.e. mainframe) to a database or between databases with different schemas.

We would just as soon work closely with one vendor and exploit whatever capabilities he can provide rather than take a lowest common denominator approach.

Rather than having a standard allowing me to switch among vendors, each providing minimal support, I would rather have one vendor who provides great support (still haven't found that vendor, though). That is the dissenting view.

Please think about how would it be to have zilions of totally different access methods to access databases... Think how would this world look like if there wouldnt be no SELECT , INSERT , UPDATE and please tell me what would be the level of bussiness oriented applications.

I deal with programming with no SELECT, INSERT, or UPDATE on a daily basis. There are large amounts of data stored in other systems, mainframes, registries, flat files, etc. that do not support SQL at all.

As I noted above, I would rather havea single vendor provide a wll supported approach than have a common denominator with other vendors whos products I do not work with.

Regardless of whatever personal definition of what is included in SQL and what is not, it is extremely difficult to port an application from once database to another. It is even more difficult to write an application of any significant size that runs with multiple vendors' databases.

I believe that is the point the article was making. Perhaps now we can have a rational discussion of whether it is important to be able to port between vendors or support multiple vendors and whether a standard is feasible.

Thanks for bringing us back to productive thinking!

Databases have diversified because of market demand, and the requirements of technological and conceptual advancements. The difficulty in maintaining a standard for such systems is that only thecompanies with the biggest r&d; budgets can affort to keep up with them, essentially closing the market to non-corporate entities. The possibilities for data management are enormous. There needs to be a way to allow functionality to grow w/o stiffling interoperability.

So, the question is - does the SQL standard need to continue to cover every possible asset, or would it be more productive to standardize on a delivery method? An attempt at this has been made with the 'extention' requirement, however it doesn't allow an administrator to strip out parts that may be incompatible with other dbs for porting.

I don't want to add to the heat here, but would like to cite interested parties to the following SQL validator:

http://developer.mimer.com/validator/index.htm

This is a web-based SQL validator that lets you paste your SQL statement into a textbox & evaluate it against the SQL-92, SQL-99 and SQL-200x standards.

As to whether PL/SQL & T-SQL 'wrap' otherwise standard SQL (FWIW) I think it's useful to distinguish procedural extensions from non-procedural ones. My understanding is that SQL is by nature and intent 'non-procedural'. That is, it's a language for describing desired outcomes for e.g., data structure creation (DDL), data retrieval/manipulation (DML), and data access permission configurations (DCL), rather than steps for achieving these desired results.

Contrast that with that the procedural language extensions that allow you to do things like loop and branch based on logical conditions, walk through a cursor, etc.

My intuition is that it's reasonable to evaluate a given vendor's SQL against standards for the non-procedural parts of a language (and there's plenty enough variation in that--look at the differences in the way mssql & oracle prior to 9i specify outer joins, for one). But the procedural extensions are so much like creating a new language that it doesn't seem profitable to me to try and pin those down to a standard accross products.

Cheers,

-Roy

I would like to add a little to the discussion.

The first thing is that a more substantive article on the exact same topic can be found at: http://www.tdan.com/i016hy01.htm
I think he brings home the point of the SQL standard being an evaporating one much more coherently.

The second is that the SQL standard specifies semantics, not syntax. For example, the first SQL specification specified that SQL compliance means you are able to create tables, columns, views but did not specify the exact syntax of the statement. As a result, the create table statement is not completely portable between vendors yet this does not mean they aren't all compliant. Many of the features listed in the article are actually SQL compliant features, Dynamic SQL being a 92 addition for example.

Thirdly, do not confuse Relational Databases with SQL. These two are not necessarily bound together and in fact SQL is not specified by Codd and Date. There are some delightfully contrarian articles writtenby Fabian Pascal that illustrate this point. Here is one of them (although not the one I was referring to)

http://searchdatabase.techtarget.com/tip/0,289483,sid13_gci788645,00.html

I also think that with the dissappearance of NIST as a standards enforcement agency that the trend in all the vendors has been to add as many proprietary features as possible to make migration as difficult as possible.

Finally, to actually advocate a greater adoption of the SQL standard may be a lost cause since the industry seems more inclined to pay lip service to it rather than meet it. For example, not one of the major database vendors has adopted all of the features in SQL 92. This is able to be conveniently overlooked since there is no longer any central agency responsible for ensuring complioance.

Thanks for listening,
Brad

You are exactly right. I got a little confused reading the article because it seemed to be saying that SQL defined the database and that isn't true. Oracle can keep its tables, rows, and columns anyway it wants and so can MySQL and so can Access.If they say they are SQL92 compatible, then you can probably use the same create table statements although if you're porting a database, you probably will be making a different schema. I don't know of any "database" that will easily import or convert to another database. You've got to be able to manipulate and modify the data, hence the extensions--they let you perform 3GL actions within the "SQL" environment. It seems to me that all the non-standard SQL is used in either conversion or an application (form/report) which sits outside of a database. Even if all databases were SQL-compliant, that doesn't mean they'd keep their internal stuff the same. All it means is that SELECT COLUMNS FROM TABLES would always work (which is true). Therest of the non-standard stuff is to improve performance and perhaps lock customers in (although I suspect the performance improvement is the real lock).

DB vendors = happy.

Sure if you hop from Oracle to SQL Server 2000, some things are similiar. Whoopee, inserts selects are similiar. But some things aren't. Giving you an incentive to stick with that DB platform. If anyone here has ever done a migration of databases for an application, you'll quickly find this the truth. What's a transform query in Oracle that was used in Microsoft Access? What's this INNER JOIN criteria in SQL Server? Its how all manufacturers give their customers a nice big cost deterrent from switching...and the money in the original manufacturers pocket.

The SQL-92 Standards conformance is required by the Feds, while the SQL-99 Standard is referred to as "An emerging Standard" in the GAO guideline. The world went to hell when Clinton dropped the FIPS-127 conformance test suite from NIST.

However, I have found that if I have a good schema design (no vendor extensions if possible), I can write fairly good portable code.

--CELKO--

Every SQL dialect has extensions, and the savvy developer can write "generic" SQL that can port between databases. . . .

Dear sir/mam,
Im graduating this semester (CS Batchelor degree)
our project is about databases and it includes SQL in Microsoft Access ,Im facing some difficulties in finding answers for some questions .if u could help me id realy appreciate it.
1. does MS Access have interpreter or compiler?
2. if it has compiler whats the compilation steps?
3. How does MS Access handle SQL errors?
4. why does each SQL statement appear in one screen?
5.can all SQL statements be in one screen?!

Thanksfor ur corporation.