Random Number Generator in T-SQL

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?

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , . Bookmark the permalink.

15 Responses to Random Number Generator in T-SQL

  1. AlexCuse says:

    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 says:

    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. 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 says:

    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. Pingback: Generate a random number in SQL Server | John Sansom - SQL Server DBA in the UK

  6. Pingback: green bay packers » Parapsychology

  7. Pingback: Weekly Link Post 97 « Rhonda Tipton’s WebLog

  8. Timmy says:

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

  9. Mark Horninger says:

    Thanks! this saved me some time this AM!

  10. Joe Brown says:

    Nice script! I helped me get what I needed. Thanks a ton!

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

  12. This help a lot! Thank you very much!

  13. none says:

    @charles: nice advert for your site!

  14. jayesh says:

    SELECT *
    FROM [dbo.table_name]
    ORDER BY NEWID();

    it simply generate random number.
    and ya it will not reapeat ID.

    enjoy no required any casting at all this it will return INT by default.

  15. Pingback: Quick and Dirty Random Data Generator | SQL Notes From The Underground

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>