Monthly Archives: July 2015

Creating Easy Test Data

T-SQL

So the other day I needed to create some test data that was fairly randomized. I didn’t like any of the examples I found online so I settled on using the RAND function. RAND gives a number between 0 and 1. If I need data that fits in a range I can simply
multiply the number of values in the range with RAND to return a value in the range.

For example if I need some ASCII I can use

SELECT CHAR(65 + FLOOR(RAND() * 25))

For multiple characters I can use this. Yeah, it isn’t elegant but gave me exactly what I needed.

SELECT CHAR(65 + FLOOR(RAND() * 25)) + CHAR(65 + FLOOR(RAND() * 25)) + CHAR(65 + FLOOR(RAND() * 25))

And for BLOBs:

SELECT REPLICATE(CHAR(65 + FLOOR(RAND() * 25)) + CHAR(65 + FLOOR(RAND() * 25)) + CHAR(65 + FLOOR(RAND() * 25)),500)

Combine these together for something like 

SELECT CHAR(65 + FLOOR(RAND() * 25)) + CHAR(65 + FLOOR(RAND() * 25)) + CHAR(65 + FLOOR(RAND() * 25)),
REPLICATE(CHAR(65 + FLOOR(RAND() * 25)) + CHAR(65 + FLOOR(RAND() * 25)) + CHAR(65 + FLOOR(RAND() * 25)),500),
DATEADD(SECOND,FLOOR(RAND() * 3024000),'2015-05-31')

Now throw in an INSERT and a GO 1000 and I’ve got some easy data to work with.

CREATE TABLE #TestData (
	ID INT IDENTITY(1,1) NOT NULL,
	SomeData NVARCHAR(100) NOT NULL,
	MaxData NVARCHAR(MAX) NOT NULL,
	DateToSeconds DATETIME NOT NULL
) 
GO

INSERT INTO #TestData (
	SomeData,
	MaxData,
	DateToSeconds
)
SELECT
	CHAR(65 + FLOOR(RAND() * 25)) + CHAR(65 + FLOOR(RAND() * 25)) + CHAR(65 + FLOOR(RAND() * 25)) AS [SomeData],
	REPLICATE(CHAR(65 + FLOOR(RAND() * 25)) + CHAR(65 + FLOOR(RAND() * 25)) + CHAR(65 + FLOOR(RAND() * 25)),500) AS [MaxData],
	DATEADD(SECOND,FLOOR(RAND() * 3024000),'2015-05-31') AS [DateToSeconds]
GO 1000

SELECT * FROM #TestData AS td ORDER BY ID
GO

Maybe not the most robust solution but it only took 10 minutes and gave me exactly what I needed.