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())