Chapter 2: Fundamental SQL Concepts and Principles

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.


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.

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



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.


Refers to the nested relationship needed to model hierarchical structures.


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


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.


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).


XML and OLAP are discussed in Chapter 17.


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.