Generating a date sequence in T-SQL using a numbers table

This is a follow-up to my article How to generate a date sequence in T-SQL.

In my experience, it is frequently required to map data to points  in time – display data on a periodic schedule, aggregate access log data by time intervals and so on. It goes without saying that this mapping should be handled as quick as possible. Of course performance matters.

In my previous post I discussed a method to generate these time points on-the-fly. Here I present a full solution for pre-generating a number sequence and generate a date sequence from it (which might be stored in a table as well).

Create a numbers table

First, create the numbers table using a Stored Procedure:

This creates the table and a unique clustered index. Locking on the Index is switched off but I guess that won’t matter much, since once created this table is only read from. Numbers always range from 0 to an upper bound, which can be set as the only parameter to the procedure.

Then, execute p_createNumbersTable to actually create a table with numbers from 0 to 1.5 million.

If the table exists, all records are purged and then recreated.  Now the table may be used to derive a date sequence from it.

To further simplify querying of the table I added a short table-valued function:

The Date Sequence Generator

Generating a date sequence is now easy as pie:

I use these date sequences only occasionally for daily or monthly reports, so generating the sequence with a function is ok. If you need similar sequences in a productive environment with high loads it might be recommended to create a static read-only table, which probably will give you an additional performance boost.

Example usage of the Date Sequence Generator

Suppose you want to retrieve the access count per user from a log table. You need a contiguous time line for each day in a month which you get by mapping non-contiguous access log results per user to a date sequence. A query would look something like so:

This is fictitious but I hope that it demonstrates how simple it gets to code a previously tedious task.