100,000 random numbers without cursor
-
Can anyone suggest a quick way to create 100,000 random numbers without using a cursor or loop ?
ie something like
declare @table table
(
random_seed llat
)
loop start
insert @table
select rand()
loop end
I'd rather
insert @table
100,000 random numbers.
Any suggestions ?
TIA
Mkulangara
Mathew J Kulangara
sqladventures.blogspot.com -
I would be surprised if there was a faster way to do it than this... (let me know if you want a different type of random number) Oh, and for 100,000 rows, it might be worth inserting them into a temp table rather than a table variable.
UPDATED, this should be more random and perform the same:
SELECT RAND(CHECKSUM(NEWID())) AS [number] FROM (SELECT 0 AS [number] UNION ALL SELECT 1 AS [number] UNION ALL SELECT 2 AS [number] UNION ALL SELECT 3 AS [number] UNION ALL SELECT 4 AS [number] UNION ALL SELECT 5 AS [number] UNION ALL SELECT 6 AS [number] UNION ALL SELECT 7 AS [number] UNION ALL SELECT 8 AS [number] UNION ALL SELECT 9 AS [number]) AS i1 CROSS JOIN (SELECT 0 AS [number] UNION ALL SELECT 1 AS [number] UNION ALL SELECT 2 AS [number] UNION ALL SELECT 3 AS [number] UNION ALL SELECT 4 AS [number] UNION ALL SELECT 5 AS [number] UNION ALL SELECT 6 AS [number] UNION ALL SELECT 7 AS [number] UNION ALL SELECT 8 AS [number] UNION ALL SELECT 9 AS [number]) AS i2 CROSS JOIN (SELECT 0 AS [number] UNION ALL SELECT 1 AS [number] UNION ALL SELECT 2 AS [number] UNION ALL SELECT 3 AS [number] UNION ALL SELECT 4 AS [number] UNION ALL SELECT 5 AS [number] UNION ALL SELECT 6 AS [number] UNION ALL SELECT 7 AS [number] UNION ALL SELECT 8 AS [number] UNION ALL SELECT 9 AS [number]) AS i3 CROSS JOIN (SELECT 0 AS [number] UNION ALL SELECT 1 AS [number] UNION ALL SELECT 2 AS [number] UNION ALL SELECT 3 AS [number] UNION ALL SELECT 4 AS [number] UNION ALL SELECT 5 AS [number] UNION ALL SELECT 6 AS [number] UNION ALL SELECT 7 AS [number] UNION ALL SELECT 8 AS [number] UNION ALL SELECT 9 AS [number]) AS i4 CROSS JOIN (SELECT 0 AS [number] UNION ALL SELECT 1 AS [number] UNION ALL SELECT 2 AS [number] UNION ALL SELECT 3 AS [number] UNION ALL SELECT 4 AS [number] UNION ALL SELECT 5 AS [number] UNION ALL SELECT 6 AS [number] UNION ALL SELECT 7 AS [number] UNION ALL SELECT 8 AS [number] UNION ALL SELECT 9 AS [number]) AS i5
SQL guy and Houston Magician
-
Additionally how wide should be the largest number?
-
Thanks Robert, but unfortunately this doesn't work for me. It turns out that in my implementation, order matters.. and in this solution... we have ascending random numbers from 0-1.
Some background first....
I am writing code that requires up to 1 million random numbers fitting the Possion distribution. I've created a function similar to Rand() that will generate a random number fitting the Poission distribution. The problem I have is that using a cursor or loop ... it is taking several hours to run.
We use the results here to predict that some event occurred, and the order of these events is important.
Also the range of the numbers acceptable is encapsulated in my function.
--MKulangara
Mathew J Kulangara
sqladventures.blogspot.com -
Well you should still be able to use Robert's CROSS JOIN method, but call your function instead of his RAND(CHECKSUM(NEWID())). The CROSS JOIN in his example is the mechanism to generate the 100,000 rows and it can be used the same way granted your function is written to return a scalar value.
-
For generating large numbers of rows, I usually use the master database and just cross join syscomments with itself a few times:
use master
go
select top 100000 rand(checksum(newid()))
from syscomments as c1
cross join syscomments as c2
cross join syscomments as c3
-
Mathew,
The cross join allows you to generate the number of rows that you need. you just need to replace the rand(checksum(newid())) part with your function.
Can you post the definition of your function? Does it require an input?
-
If I understand your comment correctly, could you not just append an:
ORDER BY 1
to the query?
SQL guy and Houston Magician
-
The system table cross join method is really slick (and it is a lot smaller!). The only downside is it adds I/O overhead to the query (~52,000 reads in SQL2k) plus you can't guarentee how many rows a db has in syscomments.
SQL guy and Houston Magician
-
Thanks guys... the system cross join did the trick... Query speed time from 2 hrs to 5 minutes.
Thanks!
MKulangara
Mathew J Kulangara
sqladventures.blogspot.com -
> Also the range of the numbers acceptable is encapsulated in my function
You better encapsulate it into static table. All possible numbers, no matter how many millions of the you've got.
Every million numbers will take under 10MB of space. I don't think it's a problem.
Then use:
SET ROWCOUNT @RowsToGet
SELECT Number
FROM Numbers
ORDER BY NEW_ID()
SET ROWCOUNT 0
It will take less than a second to proceed.
_____________
Code for TallyGenerator -
One more thought,
If you are cross joining system tables, as one poster suggested, rather than creating the values in memory then consider using the NOLOCK hint. Also be aware that there are enough rows in the system table you are using to produce the desired number of rows. To get 100,000 rows from three cross joins, you need at least 47 rows in syscomments. A new database will have 0. Rows get added to syscomments as you add views, stored procedures, and constraints to your databse.
Good luck!
SQL guy and Houston Magician
-
>> To get 100,000 rows from three cross joins, you need at least 47 rows in syscomments. A new database will have 0.
Hence why you run it in "master", where you know syscomments will have many rows for the system stored procs etc.
-
On sql server 2000 (with a few user objects ± 10)) :
SELECT COUNT(*) FROM master.dbo.SysComments
2268
So 100K with 3 cross joins is really not a problem
. -
My bad PW. I missed your comment about using master. It might be worth editing your post to qualify your object names, in case anybody makes the same mistake I did and then copies your code verbatim.
SQL guy and Houston Magician
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply