John Sansom

SQL Server DBA Blog

Generate a SQL Random Number in SQL Server

by 2 Comments

One of my favorite SQL Server authors, Michelle Ufford recently posted her method for generating a sql 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 indeed the best method to generate a random number in SQL Server.

What is the Best Way to Efficiently Generate a SQL Random Number?

Michelle proposes using the following method:

DECLARE @maxRandomValue TINYINT = 100; 
DECLARE @minRandomValue TINYINT = 0;

SELECT 
	CAST(((@maxRandomValue + 1) - @minRandomValue)
	* RAND() + @minRandomValue AS TINYINT) AS 'randomNumber';

This is an excellent method and it also performs very well indeed however, it is exposed to the issue whereby repetitive invocations of RAND() in a single query will produce the same value.

To illustrate this, executing a query such as this:

SELECT TOP 10 
	RAND() AS [RandomNumber], 
	[CustomerID], 
	[CompanyName], 
	[ContactName]
FROM [dbo].[Customers]

Produces results of the form below whereby the same random number is generated for each row:

RandomNumber         CustomerID CompanyName                  ContactName
-------------------- ---------- ---------------------------  -----------
0.21090395043234362  DSFR       SQL Server Gurus             Brent Ozar
0.21090395043234362  FDJN       All Your Problems Solved     Denny Cherry
0.21090395043234362  FDSFSD     The Meaning of Life          SQLBatman
0.21090395043234362  REWFD      It Depends                   Paul Randal

Using NEWID() When Repetitive Invocations are Required

Incorporating the use of NEWID() function within the original query eliminates this problem.

DECLARE @maxRandomValue TINYINT = 100; 
DECLARE @minRandomValue TINYINT = 0;

SELECT 
	CAST(((@maxRandomValue + 1) - @minRandomValue)
	* RAND(CHECKSUM(NEWID())) + @minRandomValue AS TINYINT)
	AS 'randomNumber';

Generating a Random Number Without Using RAND()

For an alternative method to generate a random number without using the RAND() function you can do the following:

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]

There you have it, two excellent approaches to generating random numbers in SQL Server T-SQL. I’ve no doubt that there are other excellent ways to do this and perhaps you know of some?