May 18, 2014 by Ozgur Ozden
Structured Query Language, SQL, can be considered as the main language of almost all relational database management systems (RDBMS). SQL and relational model is first introduced by Edgar F. Codd at 1970 in his paper named “A Relational Model of Data for Large Shared Data Banks”. American National Standards Institute (ANSI) standardized SQL as official standard in 1986 as and named as SQL-86. Starting from 1986 till 2011 SQL SQL’s structure revised many times. Wikipedia summarizes these changes nicely in a table as shown below:
ANSI released the first standards (ANSI X3.135-1986)
integrity constraints were added by Federal Information Processing Standards in Publication FIPS 127-1
FIPS introduced major changes in this version as FIPS 127-2.
Following features were added to the basic version: recursive queries, triggers, support for procedural and control-of-flow statements, non-scalar types, and some object-oriented features. Support for embedding SQL in Java and vice-versa.
XML features were added, window functions, standardized sequences, and columns with auto-generated values added
adds the relation between the XML and SQL. Stores XML data in an SQL database. Edit the data in the database and publish it on the XML form.
Legalizes ORDER BY outside cursor definitions. Adds INSTEAD OF triggers. Adds the TRUNCATE statement
important features introduced in this revision were: Window improvements, CALL improvements, Delete in the merge, Collection Type improvements.
After this brief introduction we can return back to SQL-99, SQL-3 and explore the new features introduced.
Development process of SQL-99 is an international joint work between ANSI and ISO. Many international committees were formed and worked on this version and released it.
Andrew (n.d) introduce these new features and divides them into two broad categories as “relational features” and “object oriented features”
New relational features introduced in SQL-99 can be list as: introducing new data types, increased security/privileges, active database, new predicates.
SQL-99 has introduced four new types of data as LARGE OBJECT (LOB), BOOLEAN, ARRAY ans ROW as composite data types and Distinct types.
SQL-99 introduced the new security features as privileges and roles can be assigned to individuals. This system increased the security and made it easier to manage the database.
SQL-99 introduced a new predicate called SIMILAR. Earlier versions of SQL has limited string search capabilities using >, < ,=< ,etc… With SIMILAR command this capability increased. Other new predicate is DISTINCT which is very similar to UNIQUE command. It is very helpful to filter the duplicate values in a columns or row.
SQL-99 introduced another new feature called trigger. This can be defined as an SQL procedure creates an action every time an event triggered in the database such as INSERT, DELETE or UPDATE. Many triggers can be created in the database to control and monitor the data.
As I have tried to explain briefly here, SQL 99 introduced many new features from new predicates to security from triggers to object oriented features. With my limited but growing knowledge in this area I see the following features are really important Object Oriented, Active directory-triggers, and XML support. Object oriented feature is important because many programming languages are becoming object oriented these days and support to these programs is vital. Active directory-trigger is also important because it gives monitoring options to the database. XML support is another new but important addition because of web compatibility.
1- Coronel, C., Morris, S. & Rob, P. (2011), Database Systems: Design, Implementation, and Management, 9th Ed, Boston, MA: Course Technology, Cengage Learning.
2- Codd, Edgar F (June 1970). "A Relational Model of Data for Large Shared Data Banks". Communications of the ACM (Association for Computing Machinery) (online) Available at: http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf [Accessed at: 20.9.2013].
3- FIPS PUB 127-2, Announcing the Standard for Database Language SQL (Online) Available at:
http://www.itl.nist.gov/fipspubs/fip127-2.htm [Accessed at: 20.9.2013]
4- SQL:1999, formerly known as SQL3”, Andrew Eisenberg and Jim Melton, SIGMOD Record, Vol. 28, No. 1, March 1999 (online) Available at http://users.dcc.uchile.cl/~cgutierr/cursos/BD/standards.pdf [Accessed at: 20.9.2013]