Simple procedure to generate sample data.

Download sample code: RandTable.zip  

This is a stored procedure that populates a specified table with random data. It’s very useful in test environment, especially, for the performance tests.

For example, you have a table:

CREATE TABLE MyTable

    ( id                    int,

      name                  varchar(30),

      e_date                datetime,

      amount                decimal(18, 2),

      quantity              decimal(18, 0)

     )

GO

Then you execute:

exec RandTable 'MyTable', 1000

and get the results:

select * from MyTable

         id name                           e_date                  amount               quantity

----------- ------------------------------ ----------------------- -------------------- -------------------

   85548330 856528677574802812345678901234 2001-09-12 00:00:00.000 4731880569350832.00  489056140010145512

    6667278 205438684218043912345678901234 2001-12-06 00:00:00.000 9914347143058528.00  159727034321640512

   93858801 078484636542349712345678901234 2000-12-15 00:00:00.000 6682601958117852.00  242443860597395612

   39653687 678900622640689812345678901234 2001-11-03 00:00:00.000 7526152292511503.00  245630079590434512

    5525168 394703769638708412345678901234 2002-02-06 00:00:00.000 2693478356886941.00  328458755382143112

   32476849 678639612962781712345678901234 2002-03-04 00:00:00.000 5891604602733461.00  82201870070537112

       ….

(1000 row(s) affected)

Few remarks:

• The length of character data is limited to 40 symbols even if type of character string is more than for example varchar(40). This limitation will be relaxed in future versions.
• Not all column data types are supported
• The performance of the population process is about 2000 rows/sec in the example above (MyTable) on Intel Core 2 CPU 6600 2.4 Ghz MSSQL 2005