I’d rather play golf

After posting my recent article on the use of the RowGenerator system table, I received a welcome email from Jan-Eike Michels of IBM who, like me, sits on the DM32.2 committee for INCITS as the IBM representative for the SQL Standard:

Hi Glenn,

Just stumbled across your blog about the RowGenerator (http://iablog.sybase.com/paulley/2009/09/using-rowgenerator/) . I don’t know whether iAnywhere supports the WITH clause but (since the standard does) you could use that one as well (similar to your sa_rowgenerator procedure):

1 2 3 4 5

WITH dummy (counter) AS (SELECT counter FROM TABLE(VALUES (1)) AS x(counter) UNION ALL SELECT counter + 1 FROM dummy WHERE counter < 1000 ) SELECT counter FROM dummy

would return 1000 rows.

I welcomed Jan-Eike’s contribution because, as he quite rightly points out, it is straightforward to generate a set of identifiers recursively using the SQL standard’s common table expression syntax, in this case using the recursive UNION construction.

One can use Jan-Eike’s example almost verbatim in SQL Anywhere. The issues with Jan-Eike’s SQL query are:

• In SQL Anywhere, one must include the RECURSIVE keyword when specifying a recursive query;
• SQL Anywhere servers do not recognize the TABLE keyword; and
• SQL Anywhere already contains a (real) table, DUMMY, that generates a single-row, single-column result set.

So here is a version of Jan-Eike’s example that generates the values between 1 and 10 in SQL Anywhere:

6
7
8
9
10

WITH RECURSIVE foo(counter) AS
  ( SELECT 1 FROM DUMMY
     UNION ALL
   SELECT counter + 1 FROM foo WHERE counter < 10 )
SELECT * FROM foo

that defines the common table expression “foo” (instead of “dummy”) and generates the specified values. The graphical plan for this query is as follows:

Some points to mention:

• Specifying a larger number of values – and hence a deeper level of recursion – may require setting the MAX_RECURSIVE_ITERATIONS connection option to a higher value.
• Jan-Eike’s example generates a sequential set of values, equivalent to what the RowGenerator system table or the sa_rowgenerator() system procedure generates. However, one could modify this query to generate a non-contiguous sequence of any values desired, simply by rewriting the SELECT list expressions in the common table expression.
• Finally, while this recursive version does have utility, the RowGenerator system table may still be a better approach. The advantage of RowGenerator is that it is a (static) base table; hence the query optimizer is much better able to estimate the cardinality of intermediate results when RowGenerator is used within a complex query than when a common table expression is used.