Chapter 2: Fundamental SQL Concepts and Principles

Ever since SQL89 was adopted as the first SQL standard, SQL aimed to be just that — the standardized, generic, nonprocedural vendor independent language of relational databases. It did succeed — to a certain extent.

Promises and Deliverables

Unlike many popular programming languages (C++, Java, Visual Basic, C#, to mention just a few), SQL was designed to be nonprocedural. That means that the features one takes for granted in any other programming language — control flow statements (IF. . .THEN), looping constructs (FOR. . .NEXT), and the like — were completely excluded.

SQL was designed for data storage, retrieval, and manipulation, and as such it was tightly coupled with database management systems (DBMS); it neither exists outside DBMS nor could it be executed without. All one has to do is to submit a query to a DBMS and receive results in some client program — either actual data from the database or status results of a task (like inserting/deleting records).

In contrast to programming-style variable manipulation, inserting, updating, and retrieving data are set-based procedures. SQL statements operate on datasets, and though an operation itself might be lengthy, it does not really have any flow. From the programmer's point of view, a SQL program is just one statement, no matter how long, that executes as a whole, or not at all.

Note

To overcome problems introduced with SQL procedural deficiency, database vendors came up with procedural extensions of their own: PL/SQL for Oracle Transact-SQL for Microsoft SQL Server, SQL PL for IBM DB2 UDB; the latest developments allow for using high-level language like Java or Visual Basic inside RDBMS. These are gradually making their way into the SQL99 standard (SQL/JRT).

Use of SQL is intertwined with the paradigm of client/server computing — long before this became a buzzword of the day. A client was supposed to know how to connect to a server, request data, and represent it for a user in some, usually graphical, format; a server is supposed to understand clients' request and return data — in addition to managing this data internally for best performance and providing safe storage and security services.

The complexity of low-level implementation — how the SQL statements are translated into machine language and executed — is hidden behind concise statements like SELECT, INSERT, or UPDATE, and the task of translating them into actual machine commands is left with the RDBMS.

This opened a whole can of worms; not only had RDBMS vendors chosen to implement different pieces of the SQL standard, but also in some cases those pieces were implemented differently. Vendor specific extensions — which often provided a base for the next iteration of SQL standard — complicated the matter even more.

As you learned in Chapter 1, the ANSI SQL standard (SQL89) instituted three levels of conformance for every database product aspiring to be ANSI compliant. SQL3 (SQL99) introduced two levels of conformance: Core SQL99 and Enhanced SQL99. Every major RDBMS is at least first (core) conformance-level compliant.

Nevertheless, SQL could hardly be considered portable; it is rather adaptable. There are quite a few points left up to the vendors to implement:

Semantic and syntactic differences.
Opening database for processing. The interfaces of ODBC, CLI, OLEDB, and others are not part of any SQL standard.
Dynamic and Embedded SQL implementations might differ from vendor to vendor.
Collating order. How results of a sorted query are presented; this depends on whether ASCII or EBCDIC characters are used. (Though the UNICODE standard alleviates this problem.)
Different data types extensions.
Differences in database catalog tables. Because this is mentioned at the full conformance level standard only; vendors working in the core level have no incentive to abandon their own proprietary structures.

The following documents constitute the main body of ANSI/ISO standards:

ANSI/ISO/IEC 9075-1:1999. Information technology — Database languages — SQL — Part 1: Framework (SQL/Framework)
ANSI/ISO/IEC 9075-1:1999/Amd 1:2001. On-Line Analytical Processing (SQL/OLAP)
ANSI/ISO/IEC 9075-2:1999. Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation)
ANSI/ISO/IEC 9075-2:1999/Amd 1:2001. On-Line Analytical Processing (SQL/OLAP)
ANSI/ISO/IEC 9075-3:1999. Information technology — Database languages — SQL — Part 3: Call-Level Interface (SQL/CLI)
ANSI/ISO/IEC 9075-4:1999. Information technology — Database languages — SQL — Part 4: Persistent Stored Modules (SQL/PSM)
ANSI/ISO/IEC 9075-5:1999. Information technology — Database languages — SQL — Part 5: Host Language Bindings (SQL/Bindings)
ANSI/ISO/IEC 9075-5:1999/Amd 1:2001. On-Line Analytical Processing (SQL/OLAP)
ANSI/ISO/IEC 9075-9:2001. Information technology — Database languages — SQL — Part 9: Management of External Data (SQL/MED)
ANSI/ISO/IEC 9075-10:2000. Information technology — Database languages — SQL — Part 10: Object Language Bindings (SQL/OLB)
ANSI/ISO/IEC 9075-13:2002. Information technology — Database languages — SQL — Part 13: SQL Routines and Types Using the Java TM Programming Language (SQL/JRT)
ANSI/ISO/IEC 9579:2000. Information technology — Remote database access for SQL with security enhancement
ANSI/ISO/IEC 13249-1:2000. Information technology — Database languages — SQL multimedia and application packages — Part 1: Framework
ANSI/ISO/IEC 13249-2:2000. Information technology — Database languages — SQL multimedia and application packages — Part 2: Full-Text
ANSI/ISO/IEC 13249-3:1999. Information technology--Database languages — SQL Multimedia and Application Packages — Part 3: Spatial
ANSI/ISO/IEC 13249-5:2001. Information technology — Database languages — SQL multimedia and application packages — Part 5: Still Image

These documents are available for a fee on the Web at www.iso.org and www.ansi.org.

Table 2-1 introduces the key SQL99 features areas that have been added to the previous SQL89, SQL92 standards. The more detailed and complete list of all major SQL99 features and compliance among major RDBMS vendors is given in Appendix J.

Table 2-1: Key SQL99 Areas
Features	Description
Call level interface (CLI)	The specification defining access to the database through a set of routines that could be called by a client application (ODBC, JDBC, etc.).
Information schema	A set of database views to access metadata for a particular database.
ROLES Security Enhancements	A security paradigm defining ability to fine-tune security privileges while grouping them into logically relevant groups.
Recursion	Refers to the nested relationship needed to model hierarchical structures.
Savepoint	An ability to add granularity to the transactional operation where a transaction could be rolled back not to the beginning but to a certain named step.
SQL Data types: BLOB, CLOB, BOOLEAN, REF, ARRAY, ROW, User Defined types	New data types to accommodate complexity of modern computing. (See Chapter 3 for in-depth discussion.)
SQL Multimedia data types: Full Text, Still Image, Spatial	New data formats developed for multimedia
SQL/MED	Defines extensions to Database Language SQL to support management of external data through the use of foreign tables and datalink data types.
SQL Programming Language	The domain ruled by proprietary procedural extensions like Oracle's PL/SQL or Microsoft Transact-SQL; defines standard programming Control-Of-Flow constructs (IF...THEN...ELSE), looping, etc.
Triggers	Defining action taken automatically in response to some predefined event; new standard fine-grained basic trigger functionality.
Management facilities for Connections, Sessions, Transactions, and Dkagnostics	Infrastructure supporting for centralized or distributed processing.

SQL is a living language; it continues to grow and adapt to ever-changing demands. Despite the market pressures to standardize features and data exchange between the databases, vendors prefer to lock their customers into a specific RDBMS package by getting them hooked on some convenient nonstandard features that, while sometimes significantly improving performance, make it hard, expensive, or altogether impossible to port SQL routines to a different RDBMS.

Every vendor is encouraged to submit papers with new ideas and ANSI/ISO committees are reviewing these on an ongoing basis, which eventually would lead to yet another SQL standard in the future. The SQL of tomorrow might not turn out to be how we imagine it to ourselves today. Some of the emerging standards include XML (eXtensible Markup Language) and OLAP (On-Line Analytical Processing).

Cross-References

XML and OLAP are discussed in Chapter 17.

Note

DB2 boasts being a major contributor to the current SQL99 standard, IBM being the number-one submitter of accepted papers for SQL99 for every single year from 1993 to 1999.