SQL Server RAND()

SQL Server RAND() Function

The SQL Server RAND() function returns a pseudo-random floating-point number between 0 and 1. This function is useful for generating random numbers for various applications.

Syntax

SELECT RAND();

The RAND() function does not take any arguments, but it can take an optional seed value:

  • SELECT RAND(seed);: The seed is an integer value used to generate a repeatable sequence of random numbers.

Example SQL Server RAND() Function Queries

Let's look at some examples of SQL Server RAND() function queries:

1. Basic RAND() Example

SELECT RAND() AS random_number;

This query returns a pseudo-random floating-point number between 0 and 1. The result will be different each time you run the query:

random_number
--------------
0.123456789

2. RAND() with a Seed Value

SELECT RAND(100) AS random_number_with_seed;

This query returns a pseudo-random floating-point number between 0 and 1 using the seed value 100. The result will be the same each time you run the query with the same seed value:

random_number_with_seed
-----------------------
0.7154366573674852

3. RAND() with a Column

SELECT id, RAND() AS random_number
FROM sample_table;

This query returns a pseudo-random floating-point number between 0 and 1 for each row in the sample_table. The result will show the id and its corresponding random_number:

id  random_number
--- --------------
1   0.234567891
2   0.987654321
3   0.456789123

4. RAND() with a Variable

DECLARE @seed INT;
SET @seed = 50;
SELECT RAND(@seed) AS random_number_with_variable_seed;

This query uses a variable to store a seed value and then returns a pseudo-random floating-point number between 0 and 1 using that seed value. The result will be the same each time you run the query with the same seed value:

random_number_with_variable_seed
-------------------------------
0.5208335734681648

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the RAND() function.

Step 1: Creating a Table

This step involves creating a new table named sample_table to store some sample data.

CREATE TABLE sample_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

In this example, we create a table named sample_table with columns for id and name.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the sample_table.

INSERT INTO sample_table (id, name) VALUES (1, 'John Doe');
INSERT INTO sample_table (id, name) VALUES (2, 'Jane Smith');
INSERT INTO sample_table (id, name) VALUES (3, 'Jim Brown');

Here, we insert data into the sample_table.

Step 3: Using the RAND() Function

This step involves using the RAND() function to return a pseudo-random floating-point number between 0 and 1 for each row in the sample_table.

SELECT id, name, RAND() AS random_number
FROM sample_table;

This query retrieves the id, name, and a pseudo-random floating-point number for each row in the sample_table. The result will be:

id  name        random_number
--- ----------- --------------
1   John Doe    0.234567891
2   Jane Smith  0.987654321
3   Jim Brown   0.456789123

Conclusion

The SQL Server RAND() function is a powerful tool for generating pseudo-random floating-point numbers between 0 and 1. Understanding how to use the RAND() function and its syntax is essential for various applications that require random number generation in SQL Server.