Have you ever needed to generate random numbers in SQL Server? The RAND() function in SQL Server can be used to generate random float values between 0 and 1 (excluding 0 and 1). However, what if you want to generate random numbers in a specific way, such as resetting the random number for each column value?

Let’s take a look at an example. Suppose we have a dataset with a column called “no” and we want to generate random numbers for each row:

CREATE TABLE #random (no INT)

INSERT INTO #random (no)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3

SELECT no, RAND() AS random_number
FROM #random

The result of this query will be:

As you can see, the random number generated is the same for all rows. But what if we want to generate a random number that is reset for each column value? This is where the seed value of the RAND() function comes into play.

The seed value is an optional parameter that can be passed to the RAND() function. By using a different seed value for each column value, we can generate different random numbers for each group.

Let’s modify our previous query to include the seed value:

SELECT no, RAND() AS random_number, RAND(no) AS random_number_reset
FROM #random

The result of this query will be:

As you can see, the values in the second column are the same for all rows, whereas the values in the third column are the same within each group (based on the column “no”), but different across groups.

This method allows you to generate random numbers differently for each group in your dataset. It can be useful in scenarios where you need to assign random values to different categories or groups.

Next time you need to generate random numbers in SQL Server, remember the seed value of the RAND() function and how it can be used to achieve different random number generation patterns.