SQL Versions

Summarize differences between these versions of SQL; i) SQL 1992, ii) SQL 1999, iii) SQL 2003, iv) SQL 2008.

7 thoughts on “SQL Versions

  1. Vinay Venkatachala

    SQL 1992-
    SQL 1992 introduced some major additions-
    • Set operations – join (natural, union), intersections
    • Creation of temporary data tables
    • Data types – DATE, TIME, VARCHAR strings, TIMESTAMP
    • Modifying schema with ALTER, DROP, REVOKE
    • Conditional expressions
    • Changing (casting) data types
    • String concatenation, substring extraction operations
    • Date and time arithmetic
    • Additional character sets support
    • Operators – LENGTH, SUBSTR, TRIM, OR (||)
    • INSERT privilege on specific columns
    SQL 1999-
    SQL 1999 was a standard published in multiple parts (5 parts). Following were the new features-
    • New built-in data types – BOOLEAN, LOBs (large objects)
    • Recursive queries
    • Updating or deleting using unions/joins
    • New predicates – SIMILAR TO, FORALL, FOR SOME
    • Additions to security options
    • User-defined types
    • Identifiers with up to 128 characters
    SQL 2003-
    SQL 2003 made revisions to the SQL 1999 and brought in some new additions.
    • BIT and BITVARYING data types were removed as there was lack of support for these data types.
    • New data types introduced – BIGINT, MULTISET, XML
    • Table functions which return tables on invocation were introduced
    • SQL schema and dynamic statements can be executed within SQL bodied as well as external routines
    • Routines can be invoked with the privileges of the invoker as well as definer
    • Columns can be defined as identity column as well as generated column
    • Enhancements to CREATE TABLE statement
    • New options for LIKE clause were added
    • MERGE statement introduced
    SQL 2008-
    SQL 2008 was the sixth revision which was adopted in July 2008. Key features are-
    • Regular expressions
    • Patter matching
    • TRUNCATE TABLE statement
    • Updates to MERGE statement
    • Addition INSTEAD OF database trigger
    • Importing and storing XML

  2. manishekar

    SQL 1992 provided major changes to its previous version. The new features of SQL 1992 compared to its predecessor are: New data types of DATE, TIME, TIMESTAMP, INTERVAL, BIT string, VARCHAR strings and NATIONAL CHARACTER strings. It supports new operations such as string concatenation, substring extension, date/time mathematics and conditional statements. It supports new operations such as UNION JOIN, NATURAL JOIN, set differences and set intersections. It also added support for conditional expressions. It provided support for ALTER and DROP. It also added support of user privileges and CHECK constraint. It provided support for dynamic execution of queries and remote execution of database. It supported creation of temporary tables, transaction isolation tables. It provided support for CAST, which is used to support data types on the fly. SQL also supported scrolled cursors.

    SQL/PSM (Persistent Stored Module) is considered to be part of SQL 1999. SQL 99 calls for a boolean data type. SQL supports distinct data types. It also supports structured user-defined types. It also added common table expressions and recursive queries. It allows only single inheritance. It also provided OLAP capabilities and role-based access control.

    SQL 2003 brought minor modifications to its previous version. It provided support for XML-related features and window functions. It provides support for the column types auto-generated values and identity-columns. It added a support for new MERGE statement and the sequence generator. It also added extensions to the CREATE TABLE. It removed BIT and BIT VARYING data types. OLAP capabilities were extended with a window function.

    The new features of SQL 2008 compared to its predecessor are transparent data encryption, which gives the ability to encrypt the entire database. It also provides to store data separately from key through external key management. It also added support for auditing and data compression. Advanced resource governor provides very good management of resources. It added support for Lines of Business Framework and Entity Query Language. It supports development query language. SQL 2008 provides the ability to pass an entire table to a stored procedure. It added support for spatial data types. It added support for MERGE command.

  3. ramya

    Some differences between SQL1992,SQL 1999,SQL 2003 & SQL 2008
    SQL 1992: Third version
    SQL 1999: Fourth version
    SQL 2003: Fifth version
    SQL 2008:Sixth version.
    SQL 1992: New data types defined: DATE, TIME, TIMESTAMP, INTERVAL, BIT string, VARCHAR strings, and NATIONAL CHARACTER strings.
    SQL 1999: Boolean data types ,Distinct user-defined types of power also called the distinct types and structured user defined data types are the new data types introduced.
    SQL 2003: removal of the poorly implemented “BIT” and “BIT VARYING” data types
    SQL 2008: No alteration of data types is done in this version.
    SQL 1992:
    1)New scalar operations such as string concatenation and substring extraction, date and time mathematics, and conditional statements.
    2)New set operations such as UNION JOIN, NATURAL JOIN, set differences, and set intersections.
    3)Support for alterations of schema definitions via ALTER and DROP.
    4)Temporary tables; CREATE TEMP TABLE etc.
    5)New integrity-checking functionality such as within a CHECK constraint.
    6)New operations for changing data types on the fly via CAST (expr AS type).
    7)A new information schema—read-only views about database metadata like what tables it contains, etc. For Eg: SELECT * FROM INFORMATION_SCHEMA.TABLES;.
    SQL 1999:
    1)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.
    2)GROUP BY was extended with ROLLUP, CUBE, and GROUPING SETS.
    3)Full support for RBAC via CREATE ROLE.
    SQL 2003:
    1)two new column types: auto-generated values and identity-columns
    2)the new MERGE statement
    3)extensions to the CREATE TABLE statement, to allow “CREATE TABLE AS” and “CREATE TABLE LIKE”
    4)OLAP capabilities (initially added in SQL:1999) were extended with a window function.

    SQL 2008:
    1)enhanced MERGE and DIAGNOSTIC statements
    2)the TRUNCATE TABLE statement
    3)comma-separated WHEN clauses in a CASE expression
    4)INSTEAD OF database triggers
    5)partitioned JOIN tables
    6)enhancements to derived column names.
    7) Additional commands such as Truncate.

  4. mykh74

    SQL (Structured Query Language) is a language developed to maintain data stored in RDBMSs. SQL was accepted as a standard by ANSI (American National Standards Institute) and ISO (International Organization for Standardization). SQL as a standard has being constantly improved by adding new features reflecting evolution of RDBMSs. The standard has different revisions.

    SQL-92 is the major revision of the standard. In this version, SQL started supporting data types such as DATE, TIME, VARCHAR, INTERVAL, and so on. String concatenation and substring extraction, date and time operations, operations for changing data types (CAST (expression AS type)) and conditional statements such as CASE expressions were introduces in this revision. Number of changes was done related to DDL. The standard includes commands such ALTER and DROP to alter schema definition. The standard allows working with temporary tables. New features were added to maintain user privileges. Database information schema became visible by querying INFORMATION_SCHEMA metadata database. Also, SQL92 includes additions related to transaction isolation levels and concurrency control, scrolled cursors, dynamic execution of queries, and compatibility between different SQL standards.

    The next big revision of SQL was SQL 1999. This version of SQL supports Boolean data types and distinct user-defined types to improve the type checking mechanism. Structured types were added into the standard as the foundation for an object-relational database. SQL 1999 added support for recursive queries and common table expressions. The standard improved role-based access control. This revision supports triggers, procedural and control-of-flow statements. In addition, SQL 1999 introduced embedded SQL in Java (SQL/OLB) and Java in SQL (SQL/JRT).

    SQL 2003 continued modifications to parts introduced in SQL 1999 and other older revisions, but this version of SQL introduced a few new features. One of them is SQL/XML extension which defines rules of usage XML inside SQL. SQL 2003 introduced windows functions, new column types such as auto-generated values and identity-columns, sequence generator, MERGE statement , CREATE TABLE extension such as CREATE TABLE AS and CREATE TABLE LIKE. This version of SQL standard extended OLAP capabilities.

    SQL 2008 added TRUNCATE statement, enhanced MERGE and DIAGNOSTIC statements, improved CASE expression by adding comma-separated WHEN clauses. This revision included INSTEAD OF triggers and partitioned JOIN tables, enhanced calculated column names. SQL 2008 supports XQuery expressions and pattern matching statements. In addition, this revision contains enhancements in XML by adding such features as importing XML data, XML manipulation inside a database, and exporting XML and SQL data into XML format.

  5. rahul

    The main attributes of SQL 1992 include
    • support of datatypes like VARCHAR, DATE, TIME, TIME STAMP etc. ,
    • support of set operations like UNION, JOIN,NATURAL JOIN, difference and intersection
    • bindings for C,ADA and MUMPs
    • support for ALTER and DROP

    The main attributes of SQL 1999 include
    Separation of standards in to framework, foundation, CLI, PSM and bindings
    • support of Boolean data type
    • Introduction of TYPE field which is similar to typedef in C
    • Support of object-relational database with structured user defined types similar to classes in OOPS. It allowed only single inheritance
    • Support for recursive queries using WITH [RECURSIVE]
    • Role based access control using CREATE ROLE
    • Online analytical processing support

    The main attributes of SQL 2003 include
    • Support of XML in conjunction with SQL- definition of XML data type
    • Online analytical processing with WINDOW clause
    • Support of MERGE statement
    • Support for “CREATE TABLE AS” and “CREATE TABLE LIKE”
    • Removal of “BIT ” and “BIT VARYING ” data types. Added BIGINT,MULTISET,XML.
    • A new schema object – sequence generator
    • Two new sets of columns – identity and generated.
    The main attributes of SQL 2008 include
    • enhanced MERGEand DIAGNOSTIC statements
    • TRUNCATE TABLE which deletes all entries in a table is introduced.
    • CASE expression with COMMA separated WHEN clauses
    • Support for Xquery,regular expression and pattern matching
    • A new database trigger INSTEAD OF is introduced

  6. leekris

    SQL-92 was designed to be a standard for relational database management systems (RDBMSs). It is based on SQL-89, which was in-turn based on SQL-86. It was the third and major revision of the standard. SQL 92 was developed by ANSI (INCTIS) Technical committee which originally began work on a SQL standard in 1982. Several new features have been included in this version. New Data types like VARCHAR, DATE, TIME, TIMESTAMP, have been introduced along with support for several other character sets beyond the basic types. It extends support to many scalar operations like string concatenation, date and time calculations, conditional expressions using CASE, integrity-checking functionality, dynamic SQL and an exclusive information schema which gives information about database metadata. Also it provides support to remote access dbases, several relational algebraic operations (Union, join) and set operations (INTERSECT, EXCEPT). There are certain divisions of features to which it provides support (like intermediate, transaction, full level features). SQL-92 does not address objects in any form but it forms basis for JDBC, SQL-1999. Important extensions of SQL-92 (before SQL-1999) are (a) SQL/CLI (ODBC) designed in 1995 and (b) SQL/PSM (Procedural Language extensions) designed in 1996.

    SQL:1999 is also known as SQL3/SQL-99 and specifies the object model. It is the fourth revision of SQL and published in 1999. It is super set of SQL-92 and is significantly larger than SQL-92. It supports User Defines data types to SQL like distinct and structured data types (analogous to classes, objects, encapsulation, polymorphic routines in objected-oriented programming languages), Boolean data types and many more.. SQL 1999 allows only single inheritance. Java objects can be stored either as an SQL BLOB (Binary Large Object) or mapped to a row in an instance of the type. SQL 1999 uses a special data type called REF for the object identification (like uniquely identifying a row of table). The SQL:1999 object model also supports table hierarchies in addition to type hierarchies which allows manipulation of default behavior for typed tables, role based access control, some extended OLAP functionalities (CUBE, ROLL UP), savepoints, recursive queries, triggers, stored procedures, user-defined functions and more object oriented features which are not included in SQL-92. Multiple standards of SQL99 are SQL/Framework (part 1), SQL/foundation (part 2), SQL/CLI (call level interface) (part 3), SQL/PSM (Persistent Stored Modules) (part 4), SQL Bindings (part 5).

    SQL:2003 is replacement for the standard,SQL:1999 and is the fifth revision of SQL. In this version, several bugs identified in SQL 1999 were fixed and enhancements to all parts of SQL 1999 are done. Some the new features are XML-related features, MERGE statement, extension of OLAP functionality with windows functions, sequence generation etc. It is structured into 9 parts out of which SQL/JRT (Java Routines and types), SQL/MED (Management of External Data), SQL/XML are not part of previous versions.

    SQL 2008 is the sixth version of ANSI standard for SQL and supports more flexible windowing functions. It is also split into several parts as that of 2003 version. New features included enhanced MERGE, Truncate table statements, instead of Triggers, support for several pattern-matching features, partitioned join tables etc. Another interesting feature of SQL 2008 is Full-Text search.

  7. sthiaga1

    SQL 1992: It is termed as SQL 2. This version of SQL addressed many of the implementation issues which were not implemented in the previous version. Three levels of conformance were defined which includes, Entry-level (SQL 89 plus basic improvements), intermediate level (core functionality), and full conformance (Complete SQL 92 features).

    SQL 1999: This continuing standardization resulted in SQL99 which was developed in the year 1999 and termed as SQL3. This version brought various versions like object – relational database concepts and integrity management. They have conformance levels which includes Core SQL99, and Enhanced SQL99.

    SQL2003: This version of standard had become commercialized by ISO and ANSI.
    SQL 2008: It is the sixth version of the SQL and it was developed in the year 2008. This standard is split into several parts, covering the framework, the foundation, the call-level interface, persistent stored modules, Management of external data, Object Language bindings, information and definition schemas, Routines, Routines and types using JAVA.

Leave a Reply