Is there a defined and accepted standard SQL language?

Question

I remember in college taking a database class where we, the students, were given the ability to choose our Database Management System. Being my first experience with databases I remember being confused that there were different syntax to the SQL statements depending upon which Database Management System used. Up until this class I was under the impression that the SQL language was a universal language that a user could use to communicate with all databases.

So what's the deal? Is there a defined SQL standard? If so, why does Database Management System manufactures differ from the specification? Was the standard designed incomplete with the idea that companies would use it as a foundation for their extensions to the language? Was the original SQL standard outdated and manufacturers needed to build more functionality? Is/was the original not updated at a pace to stay current with modern application needs? Was it to create a business model for revenue?

Thanks.

Answer 1

There are SQL standards:

http://en.wikipedia.org/wiki/SQL#Standardization

However, most DBMSs still implement a somewhat custom or extended version of the standard. (Microsoft SQL Server being the prime suspect).

Answer 2

Yes, there are SQL ANSI standards, see e.g. a draft of the SQL-92 one. However, every database vendor implements "an extended subset" of standard SQL -- and if you think about it, anything is an "extended subset" of anything else, in a sense;-). No doubt there are commercial reasons behind this, but even non-commercial implementations such as PostgreSQL behave much the same...

Answer 3

You can differ between Data Manipulation Language (SELECT, UPDATE, INSERT: SQL), Data Definition Language (CREATE, DELETE: SQL), Data Control Language (GRANT: SQL).

SQL can cover all these parts, but sometimes vendors do not like the way SQL does it. Sometimes they don't implement parts (see DCL). Even more often does the standard "trail" after the reality (see Xpath-expressions in DML) and they just create their own syntax.

Answer 4

Was the original SQL standard outdated and manufacturers needed to build more functionality? Is/was the original not updated at a pace to stay current with modern application needs? Was it to create a business model for revenue?

I think these are the main factors. The standard did not cover some useful functionality and vendors were keen to add features that would make customers choose their implementation, rather than lobbying to get it standardized as quickly as possible.

Answer 5

There is a standardized SQL. As the other replies state, the database vendors come with extensions specific to their database engines.

From my experience with databases (specially in case of large databases), trying to implement a "cross vendor" database has little meaning. The extensions in data definition language, for example, allow the developer/dba to optimize the database performance using vendor's specific features (e.g. phisical representation of the tables, details about storage of a table, index facitilites, etc). In case of queries, again, for large databases, you might need to provide hints to the database engine for building the execution plan. The syntax of the hints is, once again, specific to the vendor.

These are some examples of "non-standard" features that might have significant impact.

Again, from my experience, unfortunately, a lot of customers or technical guys (architects?) provide less attention to the database details and miss the powerfull features a database provide for data management and access.