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 (new window). 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:
- Regular SQL tables and columns of built-in types
- Regular SQL tables with some columns of built-in types and some of UDTs
- Regular SQL tables with one column whose data type is a UDT
- Typed tables
SQL:1999 is also used by SQLJ Part 2. See SQLJ (new window).
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.
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 SECTIONEXEC 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.
More on the general topic: DBMS standards
