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

16.2. Relational Solution

The use of redundant tables is a way to mimic a physical file. We immediately know, using our “set-oriented thinking,” that the table Accounts_100 needs to become a VIEW.

CREATE VIEW Accounts_100s (account_id, batch_nbr, rn)
AS
(SELECT account_id, batch_nbr, rn
   FROM (SELECT account_id, batch_nbr,
                ROW_NUMBER()
                OVER (PARTITION BY acct_nbr
                      ORDER BY posting_date, batch_nbr)
           FROM Accounts) AS A1 (account_id, batch_nbr, rn)
  WHERE MOD(A.rn, 100) = 0;

Computed data is built on the fly, rather than persisted in a base table that requires storage and has to be constantly updated to be correct. The exception to this is the data warehouse where the data will not change and needs to be accessed in the aggregate as fast as possible. ...