SQL:1999

INCITS

SQL:1999 was developed by the INCITS Technical Committee H2 on Database. This committee develops standards for the syntax and semantics of database languages.

SQL:1999¹ specifies the SQL:1999 object model. It adds User Defined Types (UDTs) to SQL. There are two types of UDTs: distinct types and structured types. A distinct type is based on a built-in data type, such as INTEGER, but whose values cannot be directly mixed in operation with that built-in type. A structured type has an internal structure such as an address type that might have street, state, and postal code attributes as part of the structure. Structured types can be given specific user-defined functions and participate in type hierarchies with other structured types. Single inheritance is supported. Multiple inheritance of interfaces (as in Java) is not supported. Both distinct and structured types can be used as the data type for columns, SQL variables, a field of a row type, or as attribute of another UDT. Java objects can be either stored in a serialized byte stream as an SQL BLOB (Binary Large OBject) or mapped to a row in typed tables. With the SQL BLOB approach, an object is stored in a cell of a table or stored as a UDT in a cell of a table. It is possible to define methods that operate on the object using either approach.

The SQL:1999 object model also allows you to declare that a UDT is the type of an entire table. Each row is an instance of the type. Each attribute of the type is transformed into a column of the table. The methods associated with the type are also associated with the table. Typed tables can have an additional self-referencing column. This is similar to the object identification (OID) used in classical object models. See object identification. The self-referencing column holds a value that uniquely identifies that row of the table. In SQL:1999, instead of using the term OID, it uses REF value with a special data type called REF type. One type can contain a REF to another type, similar to the way OIDs are used in object models. REFs can only reference rows in typed tables and cannot reference type instances in cells of a table, in variables, or anywhere else.

The SQL:1999 object model also provides for table hierarchies in addition to type hierarchies. The relationships of the table hierarchy and the type hierarchy are not necessarily one-to-one. Basically, table hierarchies allow you to modify the behavior and appearance of the default behavior for typed tables. For example, most object models assume that if you query a super type, you will also get results from any of its subtypes. Table hierarchies allow you to modify that behavior so that when you query on the super type, you do not get results from any of its subtypes.

With SQL:1999 you have multiple ways to model your data:

  1. Regular SQL tables and columns of built-in types
  2. Regular SQL tables with some columns of built-in types and some of UDTs
  3. Regular SQL tables with one column whose data type is a UDT
  4. Typed tables

SQL:1999 is also used by SQLJ Part 2. See SQLJ.

Because of backward compatibility to SQL-92, SQL:1999 supports a database sublanguage. This results in a programming style illustrated by the embedded SQL statements in the following diagram. 

Embedded database sublanguage

An example of an embedded SQL statement that creates a new Person instance would be:

EXEC SQL BEGIN DECLARE SECTION;
    char SQLSTATE[6];
    char ssan[9];
    char name[30];
EXEC SQL END DECLARE SECTION

EXEC SQL
INSERT INTO person VALUES (:ssan, :name);

This code would be in addition to any host programming code. The variables :ssan and :name would need to be set by the host program using the host programming language before execution. If you also wanted to manipulate this new Person instance in the host program, you would need programming code in addition to this code fragment that populates the instance in the programming language along with the instance in the database. 

¹ SQL:1999 is also known as SQL3 and SQL-99. SQL:1999 is the proper term. SQL3 was the working term before the specification became a standard.

Related Articles

More on the general topic: DBMS Standards

You may use this material for your work or classes. Reprint Policy. Be sure to check the menu at the left for other articles available on this site.

The Savvy Manager's Guide

Douglas K Barry is also the author of a book that explains Web Services, service-oriented architecture, and Cloud Computing in an easy-to-understand, non-technical manner.

Web Services, Service-Oriented Architectures, and Cloud Computing: The Savvy Manager's Guide

Web Services, Service-Oriented Architectures, and Cloud Computing: The Savvy Manager's Guide (Second Edition)

by with David Dick

This is a guide for the savvy manager who wants to capitalize on the wave of change that is occurring with Web Services, service-oriented architecture, and—more recently—Cloud Computing. The changes wrought by these technologies will require both a basic grasp of the technologies and an effective way to deal with how these changes will affect the people who build and use the systems in our organizations. This book covers both issues. Managers at all levels of all organizations must be aware of both the changes that we are now seeing and ways to deal with issues created by those changes.

Also by Douglas K Barry

Latest Post on the
Design Decomposition Blog
Upcoming Webinar
I will be presenting a webinar on January 21. Topic: Managing Change Needed for Service-Oriented Architectures and Cloud Computing. Description: Service-Oriented Architecture (SOA) and Cloud Computing projects are no different from other IT projects in that larger projects tend to fail and issues regarding people and change can scuttle projects. Oftentimes, such change issues are...
January 6, 2014