Generate Random Column Values to Populate a Table for Testing

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!