Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

5.1. Sequence Table

The Sequence table is a simple list of integers from 1 to (n) that is used in place of looping constructs in a procedural language. Rather than incrementing a countervalue inside a loop, we try to work in parallel with a complete set of values.

The table can include other data related to sequential numbering, such as the ordinal and cardinal number names, repeating or nonrepeating pseudo-random numbers, prime number flags, or whatever you need for your particular enterprise.

This table has the general declaration:

CREATE TABLE Sequence (seq INTEGER NOT NULL PRIMARY KEY CONSTRAINT non_negative_nbr CHECK (seq > 0), -- cardinal name VARCHAR(25) NOT NULL, -- ordinal_name VARCHAR(25) NOT NULL, ... CONSTRAINT seq_is_complete CHECK ...