Rob Farley

Rob Rob Farley has been consulting in IT since completing a Computer Science degree with first class honours in 1997. Before moving to Adelaide, he worked in consultancies in Melbourne and London. He runs the development department in one of Australia's leading IT firms, as well as doing database application consultancy and training. He heads up the Adelaide SQL Server User Group, and holds several Microsoft certifications.

Rob has been involved with Microsoft technologies for most of his career, but has also done significant work with Oracle and Unix systems. His preferred database is SQL Server and his preferred language is C#. Recently he has been involved with Microsoft Learning in the US, creating and reviewing new content for the next generation of Microsoft exams.

Over the years, Rob's clients have included BP Oil, OneLink Transit, Accenture, Avanade, Australian Electorial Commission, the Chartered Institute of Personnel and Development, the Royal Borough of Kingston, Help The Aged, Unisys, Department of Treasury and Finance (Vic), National Mutual, the Bible Society and others.

Did you mean to come here? My blog is now at

22 April 2005


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


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