I've seen many uses when you need to project 'missing data'. Eg. you have a time series (an access log for instance) and you want to show the number of hits per day for past 30 days (think analytics dashboard). If you do a
select count(...) from ... group by day you will get the count for every day, but the result will only have a row for each day you actually had at least one access. On the other hand if you first project a table of days from your numbers table (
select dateadd(day, -number, today) as day from numbers) and then you left join with the counts (or outer apply, whatever you fancy) then you will get a result that has 0 for count for the days you had no access. This is just one example. Of course, one may argue that the presentation layer of your dashboard could handle the missing days and just show a 0 instead, but some tools (eg. SSRS) will simply not be able to handle this.
Other examples I've seen used similar time series tricks (date/time +/- number) to do all sort of window calculations. In general, whenever in an imperative language you would use a for loop with a well known number of iterations, the declarative and set nature of SQL can use a trick based on a numbers table.
BTW, I feel the need to call out the fact that even though using a numbers table it feels like imperative procedural execution, don't fall into the fallacy of assuming it is imperative. Let me give an example:
for (int i=0;i<1000000;++i)
x = i;
This program will output 999999, that is pretty much guaranteed.
Lets try the same in SQL Server, using a number table. First create a table of 1,000,000 numbers:
create table numbers (number int not null primary key);
declare @i int = 0
, @j int = 0;
set nocount on;
while @i < 1000
set @j = 0;
while @j < 1000
insert into numbers (number)
set @j += 1;
raiserror (N'Inserted %d*1000', 0, 0, @i)
set @i += 1;
Now lets do the 'for loop':
declare @x int;
select @x = number
from numbers with(nolock);
select @x as [@x];
The result is:
If you're now having a WTF moment (after all
number is the clustered primary key!), the trick is called allocation order scan and I did not insert
@j*1000+@i by accident... You could had also venture a guess and say the result is because parallelism and that sometimes may be the correct answer.
There are many trolls under this bridge and I mentioned some in On SQL Server boolean operator short-circuit and T-SQL functions do no imply a certain order of execution