### What does the Transact-SQL server RAND function do?

- Aggregates a subset of random numbers
- Generate a random number
- Requests a range of numbers from a subset
- Selects an random number from subset

#### EXPLANATION

SQL Server: RAND Function

This SQL Server tutorial explains how to use the

**RAND function**in SQL Server (Transact-SQL) with syntax and examples.## Description

In SQL Server (Transact-SQL), the RAND function can be used to return a random number or a random number within a range.## Syntax

The syntax for the RAND function in SQL Server (Transact-SQL) is:RAND( [seed] )

### Parameters or Arguments

- seed
- Optional. If specified, it will produce a repeatable sequence of random numbers each time that seed value is provided.

## Note

- The RAND function will return a value between 0 and 1 (not inclusive), so value > 0 and value < 1.
- The RAND function will return a completely random number if no seed is provided.
- The RAND function will return a repeatable sequence of random numbers each time a particular seed value is used.

## Random Decimal Range

To create a random decimal number between two values (range), you can use the following formula:SELECT RAND()*(b-a)+a;Where

*a*is the smallest number and

*b*is the largest number that you want to generate a random number for.

SELECT RAND()*(25-10)+10;The formula above would generate a random decimal number between 10 and 25, not inclusive.

**TIP:**This formula would generate a random decimal number that is > 10 and < 25 but it would never return exactly 10 or 25.

## Random Integer Range

To create a random integer number between two values (range), you can use the following formula:SELECT FLOOR(RAND()*(b-a+1))+a;Where

*a*is the smallest number and

*b*is the largest number that you want to generate a random number for.

SELECT FLOOR(RAND()*(25-10+1))+10;The formula above would generate a random integer number between 10 and 25, inclusive.

**TIP:**This formula would generate a random integer number that is >= 10 and <= 25.

## Applies To

The RAND function can be used in the following versions of SQL Server (Transact-SQL):- SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

## Example of Random Number

Let's explore how to use the RAND function in SQL Server (Transact-SQL) to generate a random number between 0 and 1, not inclusive.For example:

SELECT RAND();Result:0.143811355073783(no seed value, so your answer will vary)SELECT RAND(9);Result:0.713741056982989(with seed value of 9)SELECT RAND(-5);Result:0.713666525097956(with seed value of -5)

## Example of Random Decimal Range

Let's explore how to use the RAND function in SQL Server (Transact-SQL) to generate a random decimal number between two numbers (ie: range).For example, the following would generate a random decimal value between 1 and 10, not inclusive (random number would be greater than 1 and less than 10):

SELECT RAND()*(10-1)+1;Result:5.09104269717813(no seed value, so your answer will vary)SELECT RAND(9)*(10-1)+1;Result:7.4236695128469(with seed value of 9)SELECT RAND(-5)*(10-1)+1;Result:7.42299872588161(with seed value of -5)

## Example of Random Integer Range

Let's explore how to use the RAND function in SQL Server (Transact-SQL) to generate a random integer number between two numbers (ie: range).For example, the following would generate a random integer value between 10 and 20, inclusive:

SELECT FLOOR(RAND()*(20-10+1))+10;Result:19(no seed value, so your answer will vary)SELECT FLOOR(RAND(9)*(20-10+1))+10;Result:17(with seed value of 9)SELECT FLOOR(RAND(123456)*(20-10+1))+10;Result:10(with seed value of 123456)

## 0 comments:

## Post a Comment