MS SQL Tips & Tricks

How to get a table of X random values

The simpliest way to get let's say 10 records with random value from range of 0 - 99 is to build up a query as bellow. It is easy to extend the amount of records and range by adding next join sections.

select top 10 pm1.Digit+pm2.Digit*10 as Number
from(
	select cast(1 as int) as Digit
	union select 2
	union select 3
	union select 4
	union select 5
	union select 6
	union select 7
	union select 8
	union select 9
	union select 0
	) as pm1
inner join (
		select cast(1 as int) as Digit
			union select 2
			union select 3
			union select 4
			union select 5
			union select 6
			union select 7
			union select 8
			union select 9
			union select 0
	)  as pm2 on (1=1)
order by NEWID()

Learn more tricks

Add Comment

Comments