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?