Jump to content

SQL:1999

From Wikipedia, the free encyclopedia
This is an old revision of this page, as edited by BD2412 (talk | contribs) at 21:08, 1 January 2013 (→Summary: minor fixes, mostly disambig links using AWB). The present address (URL) is a permanent link to this revision, which may differ significantly from the current revision.
This article is about SQL:1999, a revision of the SQL standard. For more information about SQL, see the main SQL article.

SQL:1999 (also called SQL 3) was the fourth revision of the SQL database query language. It introduced a large number of new features, many of which required clarifications in the subsequent SQL:2003. The latest revision of the standard is SQL:2011.

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 standard's 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:

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

New features

Data types

Boolean data types

The SQL:1999 standard calls for a Boolean type,[1] but many commercial SQL Servers (Microsoft SQL Server 2005, Oracle 9i, IBM DB2) do not support it as a column type, variable type or allow it in the results set. MySQL interprets "BOOLEAN" as a synonym for TINYINT (8-bit signed integer).[2]

Distinct user-defined types

Sometimes called just distinct types, these were introduced an optional feature (S011) to allow existing atomic types to be given a distinct meaning, allowing the type checking mechanism to detect some logical errors, e.g. accidentally adding an age to a salary. For example: 

create type age as integer FINAL;
create type salary as integer FINAL;

create two different and incompatible types. The SQL distinct types use name equivalence not structural equivalence like typedefs in C. It's still possible to perform compatible operations on (columns or data) of distinct types by using an explicit type CAST.

Few SQL systems support these. IBM DB2 is one those supporting them.[3] Oracle database does not currently support them, recommending instead to emulate them by a one-place structured type.[4]

Structured user-defined types

Main article: structured type

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.

Common table expressions and recursive queries

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

Some OLAP capablities

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

Role-based access control

Full support for RBAC via CREATE ROLE.

Notes

  1. ^ ISO/IEC 9075-2:1999 section 4.6 Boolean types
  2. ^ http://dev.mysql.com/doc/refman/5.0/en/other-vendor-data-types.html
  3. ^ http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.intro%2Fsrc%2Ftpc%2Fdb2z_distincttypes.htm
  4. ^ http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_standard_sql003.htm

Further reading

  • Jim Melton; Alan R. Simon (2002). SQL:1999: Understanding Relational Language Components. Morgan Kaufmann. ISBN 978-1-55860-456-8.
  • Jim Melton (2003). Advanced SQL, 1999: Understanding Object-Relational and Other Advanced Features. Morgan Kaufmann. ISBN 978-1-55860-677-7.
Retrieved from "https://en.wikipedia.org/w/index.php?title=SQL:1999&oldid=530811581"