

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 builtin 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 hardcoded 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 ROWNUM Pseudocolumn
The ROWNUM is a hack.
Expression with a ROWNUM in a where clause
select
ename
from Emp
where
ROWNUM = 2
is responsible for an
output that confuses a newbie.
ROWNUM predates the much
more cleanly defined row_number() analytic function. For example
select
ename, row_number() over (order by ename) num
from Emp
rownum
projects the Emp relation
to the ename column, and extends it with an additional integer
counter column. This extension, however, conflicts with the
selection operator. Both
select
ename, row_number() over (order by ename) num
from Emp
where
num = 2
and
select
ename, row_number() over (order by ename) num
from Emp
where
row_number() over (order by ename) = 2
are illegal.
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.