• Home
  • Contact

John Sansom

SQL Server DBA Blog

  • About
    • The Blog
    • John Sansom
    • Contact
  • Ebook
  • Archives
    • Start Here
    • Popular Posts
    • All Posts
    • SFTW
  • Becoming a DBA
    • What it’s Really Like
    • Interview Tips
    • Certification
    • FAQ
  • Books
  • Resources
    • Blog Tools and Technology
    • UK Events Schedule
    • References & Resource Lists
  • Subscribe

Generate a SQL Random Number in SQL Server

June 5, 2009 by John Sansom 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?

Filed Under: Performance Tuning, SQL Server Tips Tagged With: Random Number

About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!

Popular Articles

  • Top 10 Free SQL Server Tools
  • Performing fast SQL Server delete operations
  • How to identify the most costly SQL Server queries using DMV’s
  • Top 10 Junior DBA Interview Tips
  • The Database Administrator’s Primary Responsibility
  • Your Road to Becoming a DBA: Laying a Strong Foundation
  • Top 5 SQL Forums
  • SQL Server Memory Configuration, Determining MemToLeave Settings
  • Script SQL Server Agent Jobs Using PowerShell
  • Using sys.dm_os_ring_buffers to Troubleshoot Connectivity Issues

Categories

  • Administration (38)
  • Blogging (8)
  • Customer Service (5)
  • Disaster Recovery (5)
  • DMVs (4)
  • Index Optimisation (6)
  • Interviews (1)
  • Link Posts (243)
  • Memory (2)
  • Performance Tuning (15)
  • Professional Development (70)
  • Reporting Services (5)
  • Reviews (1)
  • SQL Server Community (144)
  • SQL Server Tips (11)
  • SQLServerCentral Syndication (112)
  • SQLServerPedia Syndication (116)
  • Tools (7)

Copyright © 2023 · Santech Solutions Limited · Powered by the Genesis Framework · Privacy Policy