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.