Chapter 2: Fundamental SQL Concepts and Principles - SQL Bible [Electronic resources] نسخه متنی

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.

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.

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