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.
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:- SQL/Framework
- SQL/Foundation
- SQL/CLI : an updated definition of the extension Call Level Interface, originally published in 1995, also known as CLI-95
- SQL/PSM : an updated definition of the extension Persistent Stored Modules, originally published in 1996, also known as PSM-96
- SQL/Bindings
- SQL/MED Management of External Data
- SQL/OLB Object Language Bindings
- SQL/JRT SQL Routines and Types using the Java Programming Language
New features
Data types
Boolean data types
The SQL:1999 standard calls for a Boolean type. IBM Db2 supports Boolean values since around 11.1. Microsoft SQL Server supports storage for Booleans using "BIT" data type. MySQL interprets "BOOL" and "BOOLEAN" as a mapping for its native TINYINT type. PostgreSQL provides a standard conforming Boolean type. Oracle Database has proper Boolean since Release 23.Distinct user-defined types of power
Sometimes called just distinct types, these were introduced as an optional feature 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 of distinct types by using an explicit type
CAST.Few SQL systems support these. IBM Db2 is one those supporting them. Oracle database did not support them as of 2012, recommending instead to emulate them by a one-place structured type.