Creating Random Numbers in SQL

In this SQL Server tutorial, we will go over how to use the RAND which is one of the many useful built-in function in to create random numbers in SQL Server. Once you have a database to test with let’s start exploring how to work and change this function and use it to your advantage.

How to Use the RAND function

Using the function is quiet easy. Simply call the SELECT to the function and state to return a RandomNumber, like so:

PgSQL

1

SELECTRAND();

When you submit this line of code you will get a random string of numbers. What is happening is that you are asking a computer to generate a random number sequence and the output should be an unique number. Notice though, that every time your call this function that the value will always be between 0 and 1. Not a fan of have that many decimals in your results? There is a way of getting rid of the decimal places by CASTing it to INT. In short, you must multiply that output to the integer value. Here is an example:

PgSQL

1

SELECTCAST(RAND()*1000000ASINT)AS[RandomNumber]

This will output a random number and move the decimals six spaces. This will turn the number .293847 (A given random number) into 293847.

Re-order the RAND() function output

You can also use the RAND() function to randomize the order of rows or values by using the ORDER BY RAND().

PgSQL

1

SELECT*FROMtest_tblORDERBYRAND();

With test_tbl being the name of the our table, we are requesting to search all rows and randomize the order.

Wrapping It Up

Having a Randomizing Function can be a helpful tool and save you time calculating. Let functions handle the heavy tasks and monotony for you.