Random Number Generator in T-SQL

June 4, 2009 by Michelle Ufford · 9 Comments
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: , ,