Is SQL a standard anymore?

For many companies, SQL standard conformity is an important consideration when choosing a relational database management system (RDMS), so why is this standard considered optional for database manufacturers? With the advance of proprietary features comes a decline in quality and loss of data portability—two things that benefit only one half of the vendor-client relationship. This article looks at why ANSI SQL standard support has declined and discusses the validity of the standard itself.

Why have a standard?
Technology standards are important for several reasons—number one being that consumers are assured that a product does what it is supposed to do before they buy it. Wouldn’t it be great if you could partner with a company and be assured that your data management systems would play nice? Or how about having the luxury of choosing an RDMS because it is stable, fast, and uses system resources efficiently, rather than because it supports views and transactions and the competition’s doesn’t?

There are also several benefits to having a standard—for one, third-party vendors are able to create tools and utilities that apply to an entire market rather than to a specific platform. Similarly, individuals can become certified in a standard, increasing the size of your available resource pool. Product certification can help ensure quality functionality, as opposed to the pseudo-implementation of standards requirements by compliant, but not certified, solutions.

A certified compliancy program would offer these benefits. Unfortunately there is no governing body for SQL.

Take me to your leader
For over 10 years, the National Institute of Standards and Technology (NIST), managed by the Department of Commerce, offered RDMS conformance testing. Six years ago, NIST stopped enforcing the ANSI/SQL standard, rendering it ineffective and largely ignored due to a lack of accountability for compliance.

To this day, most manufacturers claim compliance with the SQL-92 standard, named for its release in 1992. However, the current standard is actually SQL-99. Abandonment by NIST was followed by the release of a standards specification that was: (1) too broad in its scope and (2) only achievable by the top vendors in the RDMS industry due to the scope’s financial implications. These two factors strongly contributed to deviation from the standard, leading customers further and further down the proprietary road, into a data management silo—the very thing the ANSI standard was designed to prevent.

This begs the question, what’s left to call a standard?

Conformity breeds mediocrity
Various database manufacturers have taken allowances with the ANSI/SQL standard to different degrees in order to give their product a competitive advantage and meet customer demand. However, these products are still able to claim ANSI/SQL compliance. Essentially, to maintain compliance, a product must meet three conditions:

1. The manufacturer must create a flag that can be set, which issues a warning when a noncompliant query statement or functionality is used.
2. The manufacturer must refer to functions that violate the ANSI/SQL standard as “extensions” and register these features with the aforementioned flag.
3. The database must support what is referred to as “Entry Level SQL92,” which consists of the following features: Tables, Columns, Data types, key indexing, schema manipulation, row and table constraints, views, basic relational operations, and programming language bindings.

As an example, I’ve outlined some supposedly acceptable deviations below, from Oracle’s variation on SQL: PL/SQL.

PL/SQL flagged extensions:

• Array interface including the FOR clause
• SQLCA, ORACA, and SQLDA data structures
• Dynamic SQL including the DESCRIBE statement
• Embedded PL/SQL blocks
• Automatic datatype conversion
• DATE, COMP-3, NUMBER, RAW, LONG RAW, VARRAW, ROWID, and VARCHAR datatypes
• ORACLE OPTION statement for specifying runtime options
• EXEC IAF and EXEC TOOLS statements in user exits
• CONNECT statement
• TYPE and VAR datatype equivalencing statements
• AT db_name clause
• DECLARE...DATABASE, ...STATEMENT, and ...TABLE statements
• SQLWARNING condition in WHENEVER statement
• DO and STOP actions in WHENEVER statement
• COMMENT and FORCE TRANSACTION clauses in COMMIT statement
• FORCE TRANSACTION and TO SAVEPOINT clauses in ROLLBACK statement
• RELEASE parameter in COMMIT and ROLLBACK statements
• Optional colon-prefixing of WHENEVER...DO labels and of host variables in the INTO clause

I’d hoped to include a similar list for MS SQL Server’s query language, Transact-SQL. Not surprisingly, however, I was unable to find any documentation regarding specific compliance with ANSI/SQL. If you would like to know whether the function you are using in this database is consistent with the standard, you can use the ‘set fipsflagger’ option to display warning messages.

The point of this example is to show that, while database manufacturers claim to be within the parameters of the ANSI standard for RDMSs, there is no way to swap out one database for another without integration considerations, even on an application programming level.

The standard is meaningless. There’s no enforcement agency, and therefore no true compliancy. ”ANSI/SQL compliant” has become a buzzword that is only useful in a sales pitch. It has no real bearing on database usage or application development.

The ANSI SQL recommendation
Since the dissolution of NIST’s data management standards program in 1996, no certification or other mechanism exists to verify whether database manufacturers are in compliance with the ANSI SQL standard. Public demand and the honor system are all that remain to protect the best interests of paying clients, and manufacturers have opted to supply better features rather than consistency, as if there were no standard at all.

Either some sort of standard enforcement system needs to be put in place, or the myth of the SQL standard should be demoted to the level of recommendation before sloppy compliance becomes the de facto standard.