Sybase iAnywhere SQL AAnywhere Mobile and Embedded Database

I'd rather play golf

Thoughts on data management, autonomic computing, and self-managing database systems.

header image

SQL:2008 now an approved ISO International Standard

July 29th, 2008 · 2 Comments

On 17 July 2008 the ISO office in Geneva communicated the formal adoption of the ISO SQL:2008 standard, which supersedes the current ISO/ANSI SQL:2003 standard. The new standard is available for purchase from the ISO directly (either downloadable PDF or on CD-ROM) on a Part-by-Part basis….though it isn’t cheap. You can purchase the online PDF of 9075:2008 Part 2 (Foundation), easily the largest of the 9 parts of the standard, for only 442,00 Swiss Francs. The rate at ANSI’s online store is about the same at US$451.00, though member companies of ANSI get a considerable discount (a mere $360.80 for Part 2).

Here is a short summary of new additions to SQL:2008 Part 2 (Foundation), which encompasses basic SQL functionality outside of PSM language extensions (stored procedures). These are listed in “feature ID” order:

  • B035: non-extended descriptor names.
  • F122 and F123: extended DIAGNOSTIC statements.
  • F200: TRUNCATE TABLE statement. Already supported by SQL Anywhere.
  • F263: comma-separated WHEN clauses in a CASE expression. This feature permits multiple WHEN operands to be specified with a single THEN clause following.
  • F313: enhanced MERGE statement. In SQL:2008, MERGE has been extended to support multiple MATCHED and NOT MATCHED clauses, each accompanied by a search condition, that gives much greater flexibility in the coding of complex MERGE statements to handle update conflicts. All of the SQL:2008 extensions to MERGE are already supported by the Panorama release of SQL Anywhere.
  • F382: ALTER COLUMN SET DATA TYPE “data type” explicitly resets the data type of any table column.
  • F394: Optional normal form specification: explicit testing of Unicode normal forms for Unicode string values.
  • F403: Partitioned join tables.
  • F762: CURRENT CATALOG name as a system variable.
  • F763: CURRENT SCHEMA name as a system variable.
  • F841: LIKE_REGEX predicate: pattern-matching using an XQuery regular expression.
  • F842: OCCURRENCES_REGEX function: an XQuery pattern matching function that counts occurrences.
  • F843: POSITION_REGEX function: an XQuery pattern matching function that returns the string position for the match.
  • F844: SUBSTRING_REGEX function: a substring function using XQuery patterns. SQL Anywhere 11 supports a similar function, REGEXP_SUBSTR, which uses Perl-compatible regular expressions.
  • F845: TRANSLATE_REGEX function: substring substitution based on XQuery regular expression matching.
  • F846: Octet support in regular expressions.
  • F847: non-constant regular expressions.
  • F850 through F855: SQL:2008 now supports ORDER BY clauses in top-level query expressions, subqueries and views, for the purpose of using FETCH FIRST (see F856-9 below) clauses within each. This functionality is already supported by SQL Anywhere.
  • F856 through F859: FETCH FIRST clause in subqueries, views, and query expressions. The SQL:2008 syntax for restricting the rows of a result set is FETCH FIRST, rather than Microsoft SQL Server’s SELECT TOP N equivalent which SQL Anywhere supports presently.
  • S098: aggregate functions over array types.
  • S301: enhanced UNNEST clause for collection derived tables (using row types).
  • T021: BINARY and VARBINARY data types, and BINARY string literals
  • T022: Advanced support for BINARY and VARBINARY data types.
  • T023: Compound binary literals.
  • T024: Spaces in binary literals.
  • T213: INSTEAD OF triggers. INSTEAD OF triggers were introduced in SQL Anywhere 10.0.1.
  • T285: Enhanced derived column names.
  • T043 and T044: Multiplier T and Multiplier P, respectively, for specifying literal constants.
  • T101: enhanced nullability determination

Tags: SQL Anywhere · SQL Standard

2 responses so far ↓

  • 1 Mike Paola // Aug 1, 2008 at 10:32 am

    I know the question will inevitably come up with SQL Anywhere (SA) customers – how many of these features are supported in SA 11? I see that you made some references to SA above but wasn’t sure whether this was an exhaustive list.

  • 2 New Feature in Upcoming SQL Anywhere release: Support for Pirate SQL // Apr 1, 2010 at 9:53 am

    [...] SQL does have some similarities to ANSI SQL, but differs in important ways. Borrowing from classic Pirate behavior, the language is dynamic, [...]