Ever need to generate a random number in T-SQL? I have, on a couple of different occasions. I'm pretty sure that there's several different ways of doing this in T-SQL, but here's what I use:
Declare @maxRandomValue tinyint = 100
, @minRandomValue tinyint = 0;
Select Cast(((@maxRandomValue + 1) - @minRandomValue)
* Rand() + @minRandomValue As tinyint) As 'randomNumber';
This approach uses the RAND() function to generate a random seed; it also ensures that the value returned is between the specified min and max value. I've been using this method in one stored procedure that's called a couple of hundred times per second, and it seems to perform pretty well.
What method do YOU use to generate a random number? Is it faster than this method?
June 4th, 2009 - 06:56
Not sure how it stacks up speed wise (the answer is probably NOT WELL
) but one of my friends showed me this a while back:
CREATE VIEW RandomView
AS SELECT RAND() AS Val;
GO
CREATE FUNCTION RandomFloat(@MinValue INT, @MaxValue INT)
RETURNS FLOAT AS
BEGIN
RETURN (SELECT ((@MaxValue – @MinValue) * Val) + @MinValue FROM RandomView);
END
GO
Casting it to an int would be trivial. What I like about this is that unlike the builtin RAND() method it will return a different value for each row in the set.
select top 5 ROW_NUMBER() over (order by TABLE_NAME) blah
, dbo.RandomFloat(15, 100) random_works
, RAND() random_builtin
from INFORMATION_SCHEMA.TABLES
Kind of a stupid coding trick, but I find myself using something like this once in a while.
June 4th, 2009 - 07:09
This got me curious, so I did a little digging to see if there was anything a bit more production-ready and came across this (the accepted answer):
http://stackoverflow.com/questions/94906/how-do-i-return-random-numbers-as-a-column-in-sql-server-2005
June 4th, 2009 - 08:16
I don’t know about faster, but I think you’ll get better distribution and overall randomness with:
DECLARE @maxRandomValue TINYINT = 100
, @minRandomValue TINYINT = 0;
SELECT CAST(((@maxRandomValue + 1) – @minRandomValue)
* RAND(CHECKSUM(NEWID())) + @minRandomValue AS TINYINT) AS ‘randomNumber’;
June 4th, 2009 - 13:27
I use the same as Adam does:
RAND(CHECKSUM(NEWID()))
I actually got it from Itzik about 5 years ago at SQLConnections in Las Vegas.
August 12th, 2009 - 11:15
Excellent script! I modified it a tiny bit to accomplish something different, but the guts are all yours!
August 28th, 2009 - 03:32
Thanks! this saved me some time this AM!
August 27th, 2010 - 10:37
Nice script! I helped me get what I needed. Thanks a ton!
May 10th, 2011 - 14:59
You could check out the random number generators in http://www.totallysql.com/Products/SQLUtilities. Or, if you want a non-uniform distribution (eg. Normal distribution), there’s http://www.totallysql.com/Products/SQLDistributions.
September 10th, 2011 - 04:44
This help a lot! Thank you very much!