100,000 random numbers without cursor

Question

100,000 random numbers without cursor

Mathew J Kulangara

SSCommitted

Points: 1610
More actions
December 6, 2006 at 10:36 am

#115388

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

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 1

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

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Additionally how wide should be the largest number?

Mathew J Kulangara SSCommitted Points: 1610 More actions · Answer 3

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

John Rowan SSC Guru Points: 56440 More actions · Answer 4

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.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

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

Ronald San Juan SSCrazy Points: 2937 More actions · Answer 6

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?

Ronald San Juan | SQL DBA
ID 710124 ~ Code "Northwind"

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 7

If I understand your comment correctly, could you not just append an:

ORDER BY 1

to the query?

SQL guy and Houston Magician

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 8

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

Mathew J Kulangara SSCommitted Points: 1610 More actions · Answer 9

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

Sergiy SSC Guru Points: 110208 More actions · Answer 10

> 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

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 11

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 12

>> 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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

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 .

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 14

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