Time and cost for training programmers on a new relational database management system (RDBMS) are minimized.Created: March 20, 1992
ABSTRACT
The American National Standards Institute (ANSI) establishes standards for many products in the United States. This article outlines the structured query language (SQL) standards established by ANSI for relational database systems. It explains the limitations of these standards and discusses MicrosoftÒ SQL Server in the context of the ANSI standard, identifying the few areas that currently do not comply.
The American National Standards Institute (ANSI) establishes standards for many products that we depend on daily, for example, the width of doors, the position of the steering wheel in cars, and the conformity of plugs and outlets.
In the computer world, you also find standards. For example, structured query language (SQL) is the standard language for relational databases—the language through which applications communicate with the database management system. SQL (pronounced sequel) originated from research on relational database models conducted by IBMÒ in the 1970s and is now supported by a broad range of database systems, including database products for IBM’s mainframe systems.
SQL has its own standard, established by an ANSI committee after the language was created. Standardization for SQL (and for other database languages) offers several benefits:
Time and cost for training programmers on a new relational database management system (RDBMS) are minimized.
Applications can be ported to other RDBMS servers with less effort.
Applications can access remote database management systems more easily.
SQL is the basis for MicrosoftÒ SQL Server, the SybaseÒ database server for PC local area networks (LANs).
Most RDBMSs available today were designed before ANSI established a standard for SQL. Few, if any, comply 100 percent. Furthermore, the standard continues to evolve, making it a moving target. Nevertheless, SQL Server has progressed toward full compliance with each release.
In 1986, ANSI completed the first standard for SQL and published it in American National Standard for Information Systems—Database Language—SQL, Document ANSI X3.135-1986. This standard (ANSI 1986) defined two areas of compliance, Level 1 and Level 2.
On October 3, 1989, ANSI approved data integrity enhancements, which define the accuracy and reliability of information supplied through logging transactions, running stored procedures, and activating triggers. The resulting document became American National Standard for Information Systems—Database Language—SQL with Integrity Enhancement, Document ANSI X3.135-1989.
The combination of the 1986 standard and the integrity enhancement feature became ANSI 1989, the standard against which all current versions of SQL are measured.
The International Organization for Standardization (ISO) and the International Electrotechnical Commission (IEC) make up a system of national bodies for worldwide standardization (including ANSI). ISO approves standards through a consensus among 75 percent of the national bodies voting. In 1987, ISO endorsed the ANSI SQL standard in the document ISO/IEC 9075:1989 (E). Therefore, compliance with ISO is the same as compliance with ANSI.
Level 1 incorporates two categories of commands: data definition language (DDL) and data manipulation language (DML).
DDL includes CREATE TABLE and CREATE VIEW. These commands define tables and views. Column datatypes can be character, decimal, integer, smallint, or float. Identifiers (names of tables, columns, views, and so on) must support names that contain at least 12 characters.
DML contains SELECT, INSERT, UPDATE, and DELETE commands. In addition, the DML contains the transaction processing commands, which commit (COMMIT) or roll back (ROLLBACK) changes made to the data. Level 1 also calls for a cursor construct, which processes one record at a time when SQL is embedded within a procedural language such as COBOL.
In addition to fleshing out the basic command set, Level 2 introduces the GRANT command, which extends access privileges to users on objects such as tables and views.
Note:
This category of commands, which has been extended in most SQL implementations to include REVOKE, is sometimes referred to as data control language (DCL). The DCL commands handle security and permissions in the database.
Along with the DCL, Level 2 introduces the following enhancements to the basic verbs in SQL.
| Enhancement | Function |
| UNION operator | Merges two tables with the same columns and column attributes. |
| Correlated subqueries | Return more than one value. All the values returned must be connected with a table column in an outer query. |
| EXISTS operator | Checks for the existence of any rows in a subquery. |
| INSERT with a SELECT subquery | Allows records to be selected from one table and directly inserted into another. |
| UPDATE WHERE CURRENT and DELETE WHERE CURRENT | Update rows within cursors that are part of the embedded SQL language. |
| UNIQUE (on table column definition) | Rejects duplicate values when they are inserted into a column or a combination of columns. |
| NULL support | Represents missing or inapplicable information. |
| CREATE SCHEMA | Groups similar information into individual collections (databases). Contains all CREATE TABLE, CREATE VIEW, and GRANT specifications. |
The ANSI 1989 integrity enhancement calls for several constraint definitions to maintain data integrity. Because these definitions are implemented as enhancements to the DDL, this approach is sometimes referred to as declarative data integrity. Constraint definitions dictate data dependencies, such as a table that can be updated only when an item in inventory has been sold. They include:
| Constraint | Defines |
| Primary key | One or more non-null columns that uniquely identify a row in a table and are the only guaranteed way of addressing that row. Primary and foreign keys establish relationships among tables and help create joins. |
| Foreign key | One or more columns in a table that match the values of a primary key in another table. Together, the primary and foreign key constraints enforce referential integrity. |
| Check | A condition to be satisfied by each row in a table. For example:
|
| Reference | The relationship between the primary key and foreign key. |
| Default | The option chosen by the system when no other option is specified. |
The current ANSI standard excludes several significant commands and features that are considered essential and are generally found in any vendor’s version of SQL. They are:
| Command or Feature | Function |
| CREATE INDEX | Makes indexes. For aggregate functions (SUM, COUNT, MIN, and MAX), indexes retrieve data in sequence and more quickly than sorts because they do not directly access the underlying tables. |
| DROP TABLE, DROP VIEW, DROP INDEX, and DROP with any other object | Remove tables, views, indexes, and other objects. |
| ALTER TABLE | Revises tables without dropping and recreating them. In most versions of SQL, this command adds columns to tables. |
| REVOKE | Withdraws privileges from users who have been given the right to access tables or views with the GRANT command. |
| System catalog | Stores information about objects and the relationships among them, including information on tables, columns, views, indexes, and security. |
The ANSI SQL standard specifically allows for enhancements to the standard. A SQL implementation may, therefore, be considered compliant even if it contains extensions. As a result, all vendors have enhanced SQL by building in functions such as those described above. Transact-SQLÔ, the language of Microsoft SQL Server, is no exception.
Although compatible with most other commercial versions of SQL, Transact-SQL (SQL Server’s implementation of SQL) surpasses them and the ANSI standard because of its enhancements, which greatly extend the power and functionality of SQL Server. Transact-SQL has been enhanced with commands such as those mentioned in the previous section as well as with triggers, rules, stored procedures, and many other features.
The following list outlines the most important of these enhancements:
Control-of-flow language
Stored procedures
Triggers
COMPUTE clause
Data integrity mechanisms
User-defined datatypes
Very large text and image datatypes, up to 2,147,483,647 characters per field
Other noteworthy features of Transact-SQL include:
Subqueries wherever an expression is allowed
Temporary tables, which exist only for the duration of the current work session
Options to the CREATE INDEX statement for fine-tuning aspects of performance determined by indexes and for controlling the treatment of duplicate keys and rows
Options to query processing for checking how SQL Server executes queries, for estimating the complexity of a query, and for doing other functions relating to queries
User control over what happens during attempts to enter duplicate keys into a unique index or duplicate rows into a table
The ability to copy or move data from one part of a table to another part within the same table
The ability to extract data from one table and put it into another table with the UPDATE statement
The ability to remove data based on data in other tables, using a join in a DELETE statement
A fast way to delete all rows in a specified table and reclaim the freed space with the TRUNCATE TABLE statement.
Bitwise operators for use with integer and bit type columns
Dozens of built-in functions for operations on character and numeric data
Support for browse mode in a DB-Library application and for updates while browsing
These enhancements help SQL Server meet the functionality mandated by the ANSI 1989 integrity enhancement and also exceed it. SQL Server’s many data integrity features, for example, go well beyond the integrity enhancement, although they do not fully comply syntactically. Beware of products that claim syntactic compliance with the standard but don’t actually enforce the data integrity rules.
For a complete description of Transact-SQL, refer to the documents listed in the “Related Reading” section.
With a few minor exceptions, Microsoft SQL Server is ANSI compliant, as shown in Figure 1.
Figure 1. Current ANSI Compliance
To claim full compliance, a product must process DDL and DML at ANSI Level 1 or Level 2. Support for the integrity enhancement is not required for a vendor to claim compliance with Level 1 or Level 2, but support for embedded SQL is required to claim full Level 1 or Level 2 compliance. According to Section 3.4 of the standard:
An implementation claiming SQL-DML conformance shall process,
either at Level 1 or Level 2:
Direct invocation of SQL data manipulation language [DML] statements...
Module language ...
one or more of
Embedded SQL COBOL ...
Embedded SQL FORTRAN ...
Embedded SQL Pascal ...
Embedded SQL PL/I ....
From the standpoint of direct invocation, SQL Server complies with ANSI Level 1 except for decimal datatype. For details, see the next section.
Except for a single minor difference, SQL Server complies with ANSI Level 1. The only area where SQL Server differs is in offering a money datatype where ANSI Level 1 requires a decimal datatype.
Rather than a decimal datatype, SQL Server has a money datatype. The money datatype meets the standard’s precision and scale requirements, but it does not allow explicit specification of precision or scale. As a result, SQL Server cannot claim full Level 1 compliance for DDL.
The SQL Server money datatype uses an 8-byte integer scaled to four decimal places. The maximum precision allows for values between ±922,337,203,685,477.5807, which meets the needs of most users. Embedded SQL for COBOL supports decimal host variables but does not add support for decimal datatypes to SQL Server.
A cursor accesses a set of rows one at a time in embedded SQL and is indispensable in processing records with languages that contain precompilers for embedded SQL. As mentioned in Section 3.4 (quoted in the previous section, “Comparing Microsoft SQL Server with ANSI 1989”), the ANSI standard requires embedded SQL support for one or more of the following languages:
COBOL
FORTRAN
Pascal
PL/I
With the release of Microsoft Embedded SQL for COBOL, SQL Server supports cursors for COBOL applications development and compilers.
Programmers using C, Basic, or other languages can access result data directly through SQL Server’s call-level application programming interface (API), which supports functionality beyond what you can do with cursors. Starting with SQL Server version 4.2, cursors are also implemented directly within DB-Library calls.
One of the functions that exceed the standard is browse mode, which allows scanning database rows and updating their values one row at a time. Browse mode requires the underlying table(s) to have a unique index and a timestamp column. The standard does not place such requirements on cursors, nor does it specify whether changes made by other users are visible to an open cursor (or even permitted).
As in most major implementations of SQL, SQL Server does not support module language, which consists of code containing SQL statements in isolated blocks that can be accessed by other programs. But even without module language, SQL Server can claim Level 1 compliance through direct invocation.
SQL Server complies with most of ANSI Level 2. The following table shows the few areas where SQL Server differs.
| ANSI Level 2 requires | SQL Server supplies |
| UNION | — |
| WITH CHECK OPTION | — |
| WITH GRANT | — |
| CREATE SCHEMA | CREATE DATABASE |
Temporary tables make it easy to simulate the functionality of UNION, but not in the dynamic way that makes UNION so attractive. Microsoft SQL Server version 4.2 does have a UNION operator with the all option, as required by ANSI Level 2.
The following table shows areas of minor importance where SQL Server differs from ANSI Level 2.
| ANSI Level 2 requires | SQL Server supplies |
| Angle brackets (<>) to specify not equal. | An exclamation point plus an equal sign (!=) and the alternative ANSI standard (NOT=). SQL Sever version 4.2 does have angle brackets (<>). |
| The built-in variable user to return the name of the current user. | The built-in function user_name to return the name of the current user. |
| For LIKE expressions, an ESCAPE keyword that allows searching for the wildcard characters % and _. | UNIXÒ-style regular expressions to accomplish the same thing with even more power. |
| The UNIQUE constraint to be specified with the table definition. | The UNIQUE constraint to be specified on index definitions. |
| The following datatypes: numeric, real, and double precision. numeric is a synonym for decimal, real is a synonym for float, and double precision is required only to have greater precision than float. | The datatype float, which is a synonym for double precision and can be added through SQL Server’s user-defined datatype. |
| Indicator parameters for detecting null values. | The isnull function. It is not clear whether this requirement applies to direct-invocation implementations, because the syntax was obviously intended for embedded or module language implementations. But SQL Server does offer the isnull function, which fills NULL entries with the specified value. Also, DB-Library provides functions for detecting null values. |
The integrity enhancement extends the syntax of the SQL DDL to include referential integrity, default values, and user-defined constraints with table and column definitions. Referential integrity is specified by identifying columns with primary and foreign keys in the table definition.
A column definition may include a default value. Table or column definitions may, in turn, include check clauses (SQL search conditions that specify integrity constraints imposed by the user). If the table contains a row that fails a search, the constraint has been violated.
SQL Server offers these functions (and more) through triggers, defaults, and rules. Because the SQL Server approach is different, SQL Server cannot claim compliance even though it exceeds the standard. Declarative data integrity, as defined by the standard, is an elegant approach and will be supported in a future release of SQL Server. However, this approach does not allow for the support of complex business policies and rules, which are easily implemented with SQL Server’s triggers and stored procedures.
With the addition of a UNION operator (including the all option) and the real datatype, SQL Server version 4.2 has taken another step closer to full compliance with the ANSI standard. Future releases of SQL Server will become fully compliant with the ANSI SQL standard, as shown in Figure 2.
Figure 2. Future ANSI Compliance
Even with full ANSI compliance, SQL Server will continue to offer state-of-the-art extensions that build reliable, high-performance database applications.
As demonstrated in this article, today’s SQL Server complies with most of the ANSI 1989 SQL standard. In those few areas where SQL Server does not comply, it generally offers equivalent functionality.
Microsoft and Sybase remain committed to fostering the evolution of SQL Server over time into full compliance with the ANSI SQL standard. In addition, SQL Server’s Transact-SQL language offers and will continue to offer significant extensions that go well beyond the standard specification.
Microsoft SQL Server Transact-SQL User’s Guide
Microsoft SQL Server Language Reference
American National Standard for Information Systems—Database Language—SQL, Document ANSI X3.135-1986
American National Standard for Information Systems—Database Language—SQL with Integrity Enhancement, Document ANSI X3.135-1989
Information Processing System—Database Language SQL with Integrity Enhancement ISO/IEC 9075:1989 (E)
For more information about ANSI standards and the ANSI group, see your technical library or the Library of Congress.