Brettb.Com
  HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | GALLERY | CONTACT
Search: Go
TECHNICAL ARTICLES
 ASP
 ASP.NET
 JavaScript
 Transact SQL
 Other Articles
 Software Reviews

PHOTO GALLERIES
 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

TRAVEL LOG
 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

MORE STUFF
 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 Autoglass
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

POPULAR STUFF
 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

Home > Articles > Transact SQL Programming Articles

Generating Random Numbers in Transact-SQL

The Transact SQL Rand function can be used to return a random floating point number between 0 and 1:

SELECT RAND() AS RandomNumber

The Rand function can also be supplied with an integer value (i.e. smallint, tinyint or int) to use as a random seed:

SELECT RAND(@MyInteger) AS RandomNumber

Creating Random Numbers in a Certain Numerical Range

The following SQL code can be used to generate random integers between the values of the @MinValue and @MaxValue variables.

DECLARE @RandomNumber float
DECLARE @RandomInteger int
DECLARE @MaxValue int
DECLARE @MinValue int

SET @MaxValue = 4
SET @MinValue = 2

SELECT @RandomNumber = RAND()

SELECT @RandomInteger = ((@MaxValue + 1) - @MinValue) * @RandomNumber + @MinValue

SELECT @RandomNumber as RandomNumber, @RandomInteger as RandomInteger

The output of this SQL will be a random number between 2 and 12 (inclusive).

Random Numbers in SELECT Statements

An important consideration is that if the RAND function is called within a single query then it will return the same random number. You might, therefore, want to consider a different approach, such as the solution described in the article generating randomly ordered SQL Server result sets.

Useful Links

  • The SQL Documentation Tool builds technical documentation for Microsoft SQL Server databases. A free trial version is available for download.

  Site Map | Privacy Policy

All content is 1995 - 2012