Generate Random Column Values to Populate a Table for Testing

By Lori Brown | Helpful Scripts

May 06

Recently I had to do some R&D for a project and needed to generate a large table to test performance on. Since it is important to test queries on large data sets I thought that I would post how I populated a few data types in my table. To test query results I decided that I needed some values in the table that were not random so I simply salted my random data with known data at irregular intervals that would allow for testing. With a little bit of work I ended up with over 4 million records in a table with 45 columns. You can take the code below and can expand it to fit your needs. Just change the value for the @Loop variable to control how many records you want to insert into your table.

CREATE TABLE RandomLoad ([NumberColumn] int,[BitColumn] bit,[VarcharColumn] varchar(20),[CharColumn] char(1),[DateColumn] date)

*************************

–The below code will populate the RandomLoad table with random records

DECLARE @Loop INT, @Num1 INT, @Length INT

DECLARE @Varchar1 VARCHAR(20)

DECLARE @Varbin1 VARBINARY(128)

DECLARE @Char1 CHAR(2)

DECLARE @Bit1 BIT

DECLARE @Date1 DATE

SET @Loop = 0

WHILE @Loop < 1000  — number of records to generate

BEGIN

— Generate Number

SET @Num1 = ROUND(RAND() * 10000, 0)

— Generate Bit

SET @Bit1 = CRYPT_GEN_RANDOM(1)%2

— Generate Varchar

SET @Varchar1 = ”

SET @Length = CAST(RAND() * 20 AS INT) — Up to 20 characters long

WHILE @Length <> 0

BEGIN

SET @Varchar1 = @Varchar1 + CHAR(CAST(RAND() * 96 + 32 AS INT))

SET @Length = @Length – 1

END

— Generate Char

SET @Char1 = LEFT(newid(),1)

— Generate Date

SET @Date1 = CAST(GETDATE() + (365 * 2 * RAND() – 365) AS DATE)

INSERT INTO [RandomLoad] VALUES (@Num1,@Bit1,@Varchar1,@Char1,@Date1)

SET @Loop = @Loop + 1

END

GO

********************************************

Need more info or have questions please comment or contact us at SQLRxSupport@sqlrx.com and we will be happy to help!

Follow

About the Author

Lori is an avid runner, cyclist and SQL enthusiast. She has been working for SQLRX for 10 years and has been working with SQL in general for 20 years. Yup...she is an old hand at this stuff.

>