Random Number Generator in T-SQL

June 4, 2009 by Michelle Ufford
Filed under: Performance & Tuning, SQL Tips, Syndication 

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?

Tags: , ,

Comments

9 Comments on Random Number Generator in T-SQL

  1. AlexCuse on Thu, 4th Jun 2009 6:56 am

    2. 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.

  2. AlexCuse on Thu, 4th Jun 2009 7:09 am

    3. 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

  3. Adam Machanic on Thu, 4th Jun 2009 8:16 am

    4. 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’;

  4. Aaron Lowe on Thu, 4th Jun 2009 1:27 pm

    5. 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.

  5. Generate a random number in SQL Server | John Sansom - SQL Server DBA in the UK on Fri, 5th Jun 2009 4:52 am

    6. [...] authors, Michelle Ufford recently posted her method for generating a random number, in the post Random Number Generator in T-SQL. This got me thinking and I decided to do a little investigation of my own to identify what is [...]

  6. green bay packers » Parapsychology on Sat, 6th Jun 2009 3:14 am

    7. [...] Random Number Generator in T-SQL : SQL Fool [...]

  7. Weekly Link Post 97 « Rhonda Tipton’s WebLog on Sun, 7th Jun 2009 6:37 pm

    8. [...] Michelle Ufford has posted a Random Number Generator in T-SQL. [...]

  8. Timmy on Wed, 12th Aug 2009 11:15 am

    9. Excellent script! I modified it a tiny bit to accomplish something different, but the guts are all yours!

  9. Mark Horninger on Fri, 28th Aug 2009 3:32 am

    10. Thanks! this saved me some time this AM!

Tell me what you're thinking...
and oh, if you've enjoyed the post, please consider subscribing to my blog.