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 nicely? 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 is 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:
- The manufacturer must create a flag that can be set, which issues a warning when a noncompliant query statement or functionality is used.
- The manufacturer must refer to functions that violate the ANSI/SQL standard as "extensions" and register these features with the aforementioned flag.
- 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.
- 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
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.
Have your say instantly in the Tech Update forum.
Find out what's where in the new Tech Update with our Guided Tour.
Let the editors know what you think in the Mailroom.
Talkback
Good article, still relevant 2 years after it was written.