Integer Generators in SQL SQL Tips by Donald Burleson

Counting as was introduced in Chapter 1 is fundamental to our culture. Some SQL queries however require more sophistication than simply counting rows. Although sometimes there is no obvious candidate relation to count, a need may arise requiring the generation of an arbitrary number of rows and the counting of those rows. Formally, what is needed is the Integers relation which contains nothing more than a single numeric column with a list of positive integer numbers.

Surprisingly, there is no built-in Integers relation in SQL. The first half of this chapter will show about a dozen ways to cook it. And the second part of this chapter will introduce the numerous applications.

Integers Relation

Commercial databases do not come already equipped with the Integers relation. However, it is fairly easy to manufacture. Since the solution differs between various vendors, now is a good time to branch out this information.

Recursive With

DB2 has, arguably, the most satisfactory answer with recursive SQL:

with Integers (num) as
( values(1)
  union all
  select num+1 from Integers

  where num < 100
)
select * from Integers

The execution flow literally follows the formal description. The Integers relation is defined via itself, hence the adjective ?recursive?. Unlike other relational operators, which determine the result instantaneously and by purely logical means, the recursive definition works in steps. The process begins with an empty Integers relation.  Add to this, the first tuple (num=1). After that is complete the recursive part

?
  select num+1 from Integers
  where num < 100
?

is ignited and starts producing additional tuples. The recursion stops as soon as the recursive part is unable to generate more tuples.

The only imperfection with this process is the hard-coded constant in the where condition. Was the Integers relation really defined or just the IntegersThatAreLessThan100? With this question in mind, it becomes very tempting to move the whole condition to the outer query block where it fits more naturally:

with Integers (num) as
( values(1)
  union all
  select num+1 from Integers

)
select * from Integers

where num < 100

Why this is such a good idea, and yet why it does not work will be a recurring theme in this chapter.

DB2 has enjoyed the recursive SQL feature for quite a while. This solution applies to Microsoft SQL Server circa 2005 as well. A reader interested in prior art is advised to lookup Usenet Groups history on Google, since the question, ?What is the analog of rownum in a MS Server?? was asked and, perhaps, is still being asked about every week on the Microsoft SQL Server programming forum.

Big Table

There is about dozen of different ways to generate integers in Oracle. Moreover, every new release increases this number. The oldest and the least sophisticated method just hangs on the rownum column to some big table; the all_objects dictionary view being the most popular choice:

select rownum from all_objects where rownum < 100

The rownum is a pseudo column that assigns incremental integer values to the rows in the result set. This column name is Oracle proprietary syntax, hence the question from users of the other RDBMS platforms highlighted in the previous section.

The all_objects view is fairly complicated, though. From a performance perspective, the sys.obj$ dictionary table is a much better choice, because it is an actual table rather than a view.