26 February 2008

T-SQL: Random Data Generation

-- Retrieve a random row
select top 1
*
from
Employee
order by
newid()


This uses the newid() function which generates a random hex string.

For the rest of the random data, we will be using the rand() function.

Please note that you may want to use a seed with rand(). Check Books Online for a standard example of using getdate() to accomplish this.

We typically want whole numbers from rand(), constrained to a range.

We accomplish by using the following formula:

(max - min - 1) * rand() + 1

Using this formula and some functions, we can easily generate numbers, strings and dates:

-- Retrieve a whole number between @min and @max
select convert(int, ((@max - @min - 1) * rand() + 1), 0)

-- Retrieve a string of x characters (10)
declare @str varchar(10)

set @str = ''

while(len(@str) <>
begin
-- Select a number from 1 - 26,
-- add 64 (ascii offset), and convert to a character
set @str = @str
+ char(64 + convert(int, ((26 - 1 - 1) * rand() + 1), 0))
end

print @str

-- Retrieve a date between now and (now - x) for x of 99 days
select dateadd(d, convert(int, -1 * ((99 - 1 - 1)
* rand() + 1), 0), getdate())

2 comments:

Anonymous said...

Your last sql statement doesn't run as is... needs some syntax changes.

Helpful post though! Thanks & keep up the good work.

the Monk said...

All of the code is there, so copy/paste should work. For some reason, it is truncating the code instead of wrapping it. I will look into it.

Sorry for the inconvenience.