A couple of people asked that I post my iterator table and rand() function. The iterator table is basically a list of numbers 1-32767 and serves the same use as a "table of numbers." You can find good information about tables of numbers here:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
To create an iterator table:
Here is my RAND scalar UDF; it comes in handy at times for generating mock data:create table dbo.SMALL_ITERATOR
( iter smallint not null
constraint PK_SMALL_ITERATOR primary key
)
go
/* -- */
/* This routine is used to populate the small_iterator table. */
/* This query ran in 1 second in development and should run at */
/* a similar speed in production. */
/* -- */truncate table SMALL_ITERATOR
insert into small_iterator
select number from master.dbo.spt_values (nolock)
where name is null
and number <= 255insert into small_iterator
select 256 * j.iter + i.iter
from small_iterator i
inner join small_iterator j
on j.iter > 0
and j.iter <= 127
order by 256 * j.iter + i.iterdelete from small_iterator where iter = 0
select count(*) [count],
min (iter) [min iterator],
max (iter) [max iterator]
from SMALL_ITERATORgo
dbcc dbreindex (small_iterator, '', 100)
goupdate statistics small_iterator
goexec sp_recompile small_iterator
go
create view dbo.vRand
as
select rand () as vRand
go
create function dbo.rand ()
returns float
as
begin
return (select vRand from dbo.vRand)
end
go
create function dbo.randList
( @.pm_listSize integer
)
returns @.randList table
( rid integer,
iRand float
)
as
begin
declare @.upperBound integer
set @.upperBound = ceiling (convert(float, (@.pm_listSize+1))
/ convert (float, 32767))
insert into @.randList
select 32767*(j.iter-1) + i.iter - 1 as rid,
dbo.rand() as iRand
from small_iterator i (nolock)
inner join small_iterator j (nolock)
on j.iter <= @.upperBound
and 32767*(j.iter-1) + i.iter - 1 <= @.pm_listSize
and 32767*(j.iter-1) + i.iter - 1 > 0
return
end
