Posted by DickChristoph on 02/15/06 01:17

Hi Ja

Well First lets create some data. I'll create a table of Social Security
Numbers (SSN) and Names (Name 1, Name 2, etc).

Create Table People(
SSN varchar(11) not null Primary Key,
Name varchar(50))

declare @counter int
declare @f1 int
declare @f2 int
declare @f3 int
declare @c int
declare @SSN varchar(11)

set nocount on

set @Counter = 1
while @Counter <= 2000000
begin
set @F1 = 100 + cast((rand() * 900) as int)
set @F2 = cast((rand() * 100) as int)
set @F3 = cast((rand() * 10000) as int)
set @SSN = dbo.ZeroFill(@f1, 3) + '-' + dbo.ZeroFill(@f2, 2) + '-' +
dbo.ZeroFill(@f3, 4)
set @C = (Select count(*) from People where SSN = @SSN)
if @C = 0
begin
insert People(SSN, Name) values(@SSN, 'Name ' + cast(@counter as
varchar(7)))
Set @Counter = @Counter + 1
end
end

--Run Script to Here to Create DATA

Create Function ZeroFill(@num as int, @digits as int)
returns Varchar(4)
as
begin
declare @Tmp as varchar(4)
declare @n as int
set @tmp = cast(@num as varchar(4))
while len(@tmp) < @digits
begin
Set @tmp = '0' + @tmp
end
return @tmp
end


---
The Proc SelectSamplePeople will return a random list of People from the
People table.



Create Proc SelectSamplePeople(@SampleSize int)
as

set nocount on

Create table #TempPeople(
RowID int not null identity(1,1) Primary Key,
SSN varchar(11) not null)

--Order by NewId() to get People in Random Order
declare @SQL varchar(2000)
set @SQL = 'Insert #TempPeople(SSN) Select top ' + cast(@SampleSize * 25 as
varchar(12)) + ' SSN from People order by NewID()'

Exec(@SQL)

--Use t.RowID % 25 = 0 to get the 25th person from the Random List

Select p.SSN, p.Name
from People p
inner Join #TempPeople t
on p.SSN = t.SSN
where t.RowID % 25 = 0
--End of Procedure

So Now

SelectSamplePeople 20000

Will get 20000 Random People from the list.


If you forget about the every 25th Name idea, this next proc will get a
random Sample of the same size and it runs somewhat faster and uses less
temporary space.

Create Proc SelectSamplePeople1(@SampleSize int)
as

set nocount on

declare @SQL varchar(2000)
set @SQL = 'Select top ' + cast(@SampleSize as varchar(12)) + ' SSN, Name
from People order by NewID()'

Exec(@SQL)

--End of Proc

SelectSamplePeople1 20000

Will get 20000 Random People from the list

--
-Dick Christoph


"ja" <jouandae@sbcglobal.net> wrote in message
news:sj7lu150t6k5i863bem9bn8pibdep04j98@4ax.com...
> Hello,
>
> I have a table that contains approx 2 million records. I want to
> query 2,000 random records.
>
> Example: 500,000 names divided by 20,000 samples = 25. The N is 25,
> meaning samples would be sent to every 25th name on the list.
>
> this is what I am using to create a random selection
>
> order by substing(f1,4,1) + substing(f2,3,1) + substing(f3,3,1) +
> substing(f4,2,1)
>
> This method takes a very long time.
>
> Any suggestions????
>
>
> TIA
>
>
> ja

[Back to original message]