RowNum

Having used Oracle quite a bit early in my database experience, I found that I liked using rownum for all kinds of things.

Nowadays, in SQL Server, I'm very pleased to see that SQL2005 is introducing the feature to the Microsoft world. About time!

It got me thinking though, about a common situation that I come across. Let's suppose I want a list of dates between two dates, and then join that list with another table for some reason. Perhaps I want to look at the total sales that went through each day... returning 0 if there were none (rather than just skipping that day, which is what would happen if I were to only look at the Sales table).

At the moment, I tend to use a table that I just store numbers in for this type of purpose. Like this:

select * from dbo.numbers

nums
----
0
1
2
3
4
5
...

Then I can write a query like this:

select dateadd(day,num.number,@startdate) theDate, sum(sales.amount) theSales
from numbers num (NOLOCK)
     left join salesdata sales (NOLOCK)
          on sales.saledate >= dateadd(day,num.number,@startdate)
          and sales.saledate < dateadd(day,num.number+1,@startdate)
where num.number <= datediff(day,@startdate,@enddate)
group by num.number

This query will give me zeros for weekends, public holidays, whenever no sales are made. Makes it display much more consistently in a report.

If I think I'm going to use more numbers than I have stored, I can always call a top-up function first, to check how high my numbers go and then add more as required. Or I could use a function to create the table on the fly... but I'm not so keen on this idea, as disk-space for a table is typically cheaper than the processing time involved in creating a temporary table each time.

Using rownum, I could have a table of bit values, which could be stored much smaller. I would just have to have enough of them in the table, checking count(*) instead of max(number) in my top-up function.

My query would then become:

select dateadd(day,rownum,@startdate) theDate, sum(sales.amount) theSales
from bits (NOLOCK)
     left join salesdata sales (NOLOCK)
          on sales.saledate >= dateadd(day,rownum,@startdate)
          and sales.saledate < dateadd(day,rownum+1,@startdate)
where rownum <= datediff(day,@startdate,@enddate)
group by dateadd(day,rownum,@startdate)

But you can see that rownum appears in the 'group by' statement, which isn't healthy at all.

Also, if I want to return multiple records for each day, I have an issue, because rownum will increase by one on each row. Really, I need a subquery, like this:

select dateadd(day,num.number,@startdate) theDate, sum(sales.amount) theSales, sales.salesman
from (select rownum as number from bits (NOLOCK) where rownum <= datediff(day,@startdate,@enddate)) num
     left join salesdata sales (NOLOCK)
          on sales.saledate >= dateadd(day,rownum,@startdate)
          and sales.saledate < dateadd(day,rownum+1,@startdate)
group by sales.salesman, num.number