SQL:1999 (also called SQL 3) 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.
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:
- SQL/Framework ISO/IEC 9075-1:1999
- SQL/Foundation ISO/IEC 9075-2: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/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/Bindings ISO/IEC 9075-5:1999
Three more parts, also considered part of SQL:1999 were published subsequently:
- SQL/MED Management of External Data (SQL:1999 part 9) ISO/IEC 9075-9:2001
- 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
This section needs expansion. You can help by adding to it. (November 2012)
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.
Distinct user-defined types of power
Sometimes called just distinct types, these were introduced as an optional feature (S011) to allow existing atomic types to be extended with a distinctive meaning to create a new type and thereby enabling 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;
creates 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
Structured user-defined types
Common table expressions and recursive queries
Some OLAP capabilities
GROUP BY was extended with ROLLUP, CUBE, and GROUPING SETS.
Role-based access control
Full support for RBAC via CREATE ROLE.
SQL:1999 introduced the UNNEST keyword.
- ISO/IEC 9075-2:1999 section 4.6 Boolean types
- "MySQL :: MySQL 5.0 Reference Manual :: 11.4 Using Data Types from Other Database Engines". Dev.mysql.com. 2010-01-09. Retrieved 2014-01-30. CS1 maint: discouraged parameter (link)
- PostgreSQL documentation about Boolean Type
- "IBM Information Management Software for z/OS Solutions Information Center". Publib.boulder.ibm.com. Retrieved 2014-01-30. CS1 maint: discouraged parameter (link)
- "Oracle Compliance To Core SQL:2003". Docs.oracle.com. Retrieved 2014-01-30. CS1 maint: discouraged parameter (link)
- Jones, Arie; Stephens, Ryan K.; Plew, Ronald R.; Garrett, Robert F.; Kriegel, Alex (2005). "Appendix B ANSI and Vendor Keywords". SQL Functions Programmer's Reference. John Wiley & Sons. p. 680. ISBN 9780764598074. Retrieved 2016-05-16. CS1 maint: discouraged parameter (link)