kSQL 

Random Number Generator

We've all had to generate random data for testing applications. I wanted to share a few strategies of my own. Most of these I'll use in a loop with however many iterations equalling how many rows I want inserted.This first example is my random number generator. The nifty part of this guy is it gives you a positive number between 1 and 100. If you change "100" to "500" then your range expands from 1 to 500.

SELECT ABS(CAST(NEWID() AS BINARY(6)) % 100) + 1;

My second example was used when I was working in our financial system. I suspect the below code may be overcomplicated but it did the trick for me. Your output will be in the format of 1000.00. It will always return dollars and .00 for cents. Making the "40" larger or smaller will expand or limit the range of monetary values.

SELECT CONVERT(MONEY,CEILING(RAND()*CONVERT(INT,DATEPART(MS,GETDATE())*40));

Last, a random text generator. Obviously you can tweak this query by using the LEN function or other filters. It uses spt_values, which I'm quickly coming to love.

SELECT TOP 10 UPPER([name])
FROM [master].[dbo].[spt_values]
WHERE [name] IS NOT NULL
ORDER BY NEWID();