Structured Query Language/SQL: The Standard ISO IEC 9075 and various Implementations

From Wikibooks, open books for an open world
Jump to: navigation, search

← SQL: A Language for Working with rDBMS | Create a simple Table →

Benefit of Standardization[edit]

Like most other standards the main purpose of SQL is portability. Usually software designers and application developers structure and solve problems in layers. Every abstraction level is realized in its own component or sub-component: presentation to end user, business logic, data access, data storage, net and operation system demands are typical representatives of such components. They are organized as a stack and every layer offers an interface to the upper layers to use its functionality. If one of those components is realized by two different providers and both offer the same interface (as an API, Web-Service, language specification, ...) it is possible to exchange them without changing the layers which are based on them. In essence the software industry needs stable interfaces at the top of important layers to avoid dependence on a single provider. SQL acts as such an interface to relational database systems.

If an application uses only those SQL commands which are defined within standard SQL, it should be possible to exchange the underlaying rDBMS with a different one without changing the source code of the application. In practice this is a hard job, because concrete implementations offer numerous additional features and software engineers love to use them.

A second aspect is the conservation of know how. If a student learns SQL, he is in a position to develop applications which are based on an arbitrary database system. The situation is comparable with any other popular programming language. If one learns Java or C-Sharp, he can develop applications of any kind running on a lot of different hardware systems and even different hardware architectures.

Limits[edit]

Database systems consist of many components. The access to the data is an important but not the only component. Additionally there are many more tasks: throughput optimization, physical design, backup, distributed databases, replication, 7x24 availability, ... . Standard SQL is focused mainly on data access and ignores typical DBA tasks. Even the CREATE INDEX statement as a widely used optimization strategy is not part of the standard. Nevertheless the standard fills thousands of pages. But most of the DBA's daily work is highly specialized to every concrete implementation and must be done in a different way when he switches to a different rDBMS. Mainly application developers benefit from SQL.

The Standardization Process[edit]

The standardization process is organized in two levels. The first level acts in a national context. Interested companies, universities and persons of one country work within their national standardization organisation like ANSI, Deutsches Institut für Normung (DIN) or British Standards Institution (BSI), where every member has one vote. The second level is the international stage. The national organizations are members of ISO respectively IEC. In case of SQL there is a common committee of ISO and IEC named Joint Technical Committee ISO/IEC JTC 1, Information technology, Subcommittee SC 32, Data management and interchange, where every national body has one vote. This committee approve the standard under the name ISO/IEC 9075-n:yyyy, where n is the part number and yyyy is the year of publication. The nine parts of the standard are described in short here.

If the committee releases a new version, this may concern only some of the nine parts. So it is possible that the yyyy denomination differs from part to part. Core SQL is defined mainly by the second part: ISO/IEC 9075-2:yyyy Part 2: Foundation (SQL/Foundation) - but it contains also some features of other parts.


Note: The API JDBC is part of Java SE and Java EE but not part of the SQL standard.


The standard is complemented by a second, closely related standard: ISO/IEC 13249-n:yyyy SQL Multimedia and Application Packages, which is developed by the same organizations and committee. This publication defines interfaces and package based on SQL. They focus on special kind of applications: text, pictures, data mining and spatial data applications.

Verification of Conformance to the Standard[edit]

Until 1996 the National Institute of Standards and Technology (NIST) certified the compliance of the SQL implementation of rDBMS with the SQL standard. As NIST abandon this work, nowadays vendors self-certify the compliance of their product. They must declare the degree of conformance in a special appendix of their documentation. This documentation may be voluminous as the standard defines not only a set of base features - called Core SQL:yyyy - but also a lot of additional features an implementation may conform to or not.

Implementations[edit]

To fulfill their clients' demands all major vendors of rDBMS offers - among other data access ways - the language SQL within their product. The implementations cover Core SQL, a bunch of additional standardized features and a huge number of additional, not standardized features. The access to standardized features may use the regular syntax or an implementation specific syntax. In essence SQL is the clamp holding everything together, but normally there are a lot of detours around the official language.