SQL (Structured Query Language) for relational databases was maintained and standardised by the American National Standards Institute (ANSI) and the International Organisation for Standardisation (ISO) which is affiliated with the International Electrotechnical Commission (IEC).

When a new SQL standard is published by these organisations it is outlined on the ISO website and available for everyone to both reference and use as required.

The current version used in most production instances is commonly ISO/IEC 9075-11:2011; however, in December of 2016 a new version was published and is described under ISO/IEC 9075-11:2016 which can be found here: https://www.iso.org/standard/63565.html

The latest version includes but is not limited to the following changes:

  • The structure and integrity constraints of SQL-data.
  • The security and authorization speci?cations relating to SQL-data.
  • The features and subfeatures of ISO/IEC 9075, and the support that each of these has in an SQL-implementation.
  • The SQL-implementation information and sizing items of ISO/IEC 9075 and the values supported by an SQL-implementation.

    • It is important to note that ISO states on their website that ISO/IEC 9075-1, ISO/IEC 9075-2 and ISO/IEC 9075-11 encompass the minimum requirements of the SQL language (2011) and predating new specifications, one should include them.

    Among the most recent standards additions, the following three points are of particular interest to me as I believe them to add a great amount of value.

    JSON Support

    Javascript Object Notation or JSON for short has become a vital datatype in the programming world due to it?s simplicity and sheer power of conveying nested data which can easily be cast to and from standard String types.

    Being able to store JSON directly into a database and query it using SQL is highly advantageous to developers. The SQL standard states that there is no set JSON type and that JSON should simply be stored as a String; however, they do allow database vendors to provide standard JSON functions for native support (Winand, 2017).

    Date/Time Formatting

    Being able to format dates has always been a bit of a challenge in SQL and vendors have often dealt with the problem differently due to the lack of standards; finally as of SQL:2016 there is now a standard to this functionality.

    Being able to use TIMESTAMP, DATE and TIME respectively across all database engines (Kulkarni, 2012) that acknowledge the new standard makes for a more seamless query-life eco-system.

    Trigonometric and Logarithmic Functions

    With the introduction of both trigonometric and logarithmic functions (ANSI, 2016), more advanced mathematical operations can be done directly in the database which frees up the application stack from having to perform these calculations.

    Being able to perform all required operations in the database engine is much faster than an external application having to retrieve data, perform operations on it and then use that to continue performing operations on the database.

    Removing this hurdle allows the database engine the power to make it?s own decisions when performing operations on localised data.

    References

    ISO (2011) ISO/IEC 9075-11:2011 – Information technology — Database languages — SQL — Part 11: Information and Definition Schemas (SQL/Schemata) [Online] ISO.org, Available from: https://www.iso.org/standard/53685.html (Accessed on 12th November 2017)

    Winand, M. (2017) What’s New in SQL:2016 [Online] Modern-sql.com, Available from: http://modern-sql.com/blog/2017-06/whats-new-in-sql-2016 (Accessed on 12th November 2017)

    Kulkarni, K. (2012) Temporal features in SQL:2011 [Online] cs.ulb.ac.be, Available from: https://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf (Accessed on 12th November 2017)

    ANSI (2016) New Edition of Database Language SQL Standard Published [Online] ANSI.org, Available from: https://share.ansi.org/Shared%20Documents/News%20and%20Publications/Links%20Within%20Stories/SQL%20standard%20published_POST.pdf (Accessed on 12th November 2017)