ⓘ SQL:1999


ⓘ SQL:1999

SQL:1999 was the fourth revision of the SQL database query language. It introduced many new features, many of which required clarifications in the subsequent SQL:2003. In the meanwhile SQL:1999 is deprecated.


1. Summary

The ISO standard documents were published between 1999 and 2002 in several installments, the first one consisting of multiple parts. Unlike previous editions, the standards name used a colon instead of a hyphen for consistency with the names of other ISO standards. The first installment of SQL:1999 had five parts:

  • SQL/Framework ISO/IEC 9075-1:1999
  • SQL/Bindings ISO/IEC 9075-5:1999
  • SQL/PSM: an updated definition of the extension Persistent Stored Modules, originally published in 1996, also known as PSM-96 ISO/IEC 9075-4:1999
  • SQL/CLI: an updated definition of the extension Call Level Interface, originally published in 1995, also known as CLI-95 ISO/IEC 9075-3:1999
  • SQL/Foundation ISO/IEC 9075-2:1999

Three more parts, also considered part of SQL:1999 were published subsequently:

  • SQL/OLB Object Language Bindings SQL:1999 part 10 ISO/IEC 9075-10:2000
  • SQL/JRT SQL Routines and Types using the Java Programming Language SQL:1999 part 13 ISO/IEC 9075-13:2002
  • SQL/MED Management of External Data SQL:1999 part 9 ISO/IEC 9075-9:2001

2.1. New features Boolean data types

The SQL:1999 standard calls for a Boolean type, but many commercial SQL servers Oracle Database, IBM DB2 do not support it as a column type, variable type or allow it in the results set. Microsoft SQL Server is one of the few database systems that properly supports BOOLEAN values using its "BIT" data type. Every 1–8 bit fields occupies one full byte of space on disk. MySQL interprets "BOOLEAN" as a synonym for TINYINT 8-bit signed integer. PostgreSQL provides a standard conforming Boolean type


2.2. New features Structured user-defined types

These are the backbone of the object-relational database extension in SQL:1999. They are analogous to classes in objected-oriented programming languages. SQL:1999 allows only single inheritance.


2.3. New features Common table expressions and recursive queries

SQL:1999 added a WITH construct allowing recursive queries, like transitive closure, to be specified in the query language itself; see common table expressions.


2.4. New features Some OLAP capabilities

GROUP BY was extended with ROLLUP, CUBE, and GROUPING SETS.