Generating Test Data: Part 1 - Generating Random Integers and Floats

WITH
cteRowNumberGenerator AS
(--==== Prevents sorting all the rows of the cross join.
SELECT TOP (1000) --Put your desired number here
UniqueNumber = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)--==== Randomly sorts just the numbers generated from above.
SELECT UniqueNumber
FROM cteRowNumberGenerator
ORDER BY NEWID()
;

Jeff Moden (3/26/2012)

Michael Valentine Jones (3/26/2012)

Here is an alternate method that I use to generate the pseudo random numbers. The basic method is to take the right 7 bytes from the NEWID function and convert that to a BIGINT before applying the MODULUS operator. No need for the ABS function, since 7 bytes can only produce a positive BIGINT number.


if object_id('tempdb..#t','U') is not null begin drop table #t end

-- Generate 20,000,000 rows
select top 20000000
	NUMBER = identity(int,1,1)
into
	#t
from
	(select top 4473 * from master.dbo.syscolumns) a
	cross join
	(select top 4473 * from master.dbo.syscolumns) b

-- Show distribution of rowcount around average of 40000
select
	a.RandomNo,
	Rows = count(*)
from
	(
	select
		RandomNo =
		(convert(bigint,convert(varbinary(7),newid()))%500)+1
	from
		#t aa
	) a
group by
	a.RandomNo
order by
	count(*),
	a.RandomNo

RandomNo             Rows        
-------------------- ----------- 
                 335       39455 
                   3       39457 
                  76       39481 
                 426       39489 
                 494       39535 
                 242       39539 
                 278       39539 
                 490       39548 
                 445       39553 
                 244       39566 
...
...
...
                 124       40400 
                 228       40402 
                 425       40410 
                 286       40434 
                  45       40458 
                 463       40463 
                 373       40531 
                 152       40586 

(500 row(s) affected)

Like I said in the article, the conversion to VARBINARY will slow things down and to no good end if you don't really need BIGINT for the random integer. If you really want BIGINT capability (and I realize that wasn't one of your goals in your example), I believe you'd also have to convert the whole NEWID() to VARBINARY.

I also thought you were involved in some testing that showed the use of the square root of the final number of desired rows as a TOP for the self joined table in the Cross Join really wasn't worth it.

The main point that I'm trying to make is that if it's too complicated, folks won't use it.