I have been participating at AskSqlTeam.com lately. One of the recent question was about how to generate date for 1 year. TG had an interesting solution and I did not realize it was set-operation until Kristen set me straight. :) As usual, I was coming up with a iterative solution as opposed to set-based one. Here is the snippet I modified off of TG's code.

create table #myTable

(dateCol datetime)

go

declare @i int

SET @i =0

WHILE

(

datediff(year, dateadd(day, @i,'2010-01-01'),'2010-01-01')=0

)

begin

insert #myTable (dateCol)

select dateadd(day, @i,'2010-01-01')

SET @i = @i +1

continue

end

go

select*from#myTable

go

The script above will create about 365 entries containing everyday of the year 2010.

TG's answer to that problem was:

create table #myTable

(dateCol datetime)

go

insert #myTable (dateCol)

select dateadd(day, number,'2010-01-01')

from master..spt_values

where type ='P'and number <365

order by number

go

select*from#myTable

go

Anyways, my approach above is not the most ideal solution and it will be slow because of the looping. TG had the right idea. Many DB professional create a reference database for their tasks. In this database one may have scripts that can be applied on scheduled jobs and other artifacts that don't belong anywhere else. The solution above can be improved if we create a reference table containing all integers. Let's say we call that table IntValues. I started building that table today and it took more than 6 hours to enter 2 billion numbers. Here is the final draft of what that script would look like.