SQL 2005+

Generating Random Numbers in T-SQL Queries

by Richard Carr, published at http://www.blackwasp.co.uk/SqlSelectRandom.aspx

There are many reasons for generating random numbers in Transact-SQL (T-SQL) queries but the standard randomisation function will not generate a new value for each row. However, there is a workaround for this problem.

Random Numbers

As part of the SQL Server Programming Fundamentals tutorial I explained the use of the Rand function for generating pseudo-random numbers. This function is useful when you need a single random number. Unfortunately, when used to generate a column value in a query, only one random number is produced. This value is repeated for every row in the query's results, where you might have expected to obtain a new value in each row.

We can demonstrate this with a simple example. Start by creating and populating a test table using the script below:

CREATE TABLE Test
(
    Name VARCHAR(20)
)

INSERT INTO Test VALUES ('Bob')
INSERT INTO Test VALUES ('Mel')
INSERT INTO Test VALUES ('Sam')
INSERT INTO Test VALUES ('Jim')
INSERT INTO Test VALUES ('Sue')
INSERT INTO Test VALUES ('Tim')

We can now run a query against the table that includes a randomised value. The following generates a result set with two columns. The first is a random value and the second is the Name from the test table. As you can see in the example results, the same random value appears in every row. Repeating the query will likely yield a different result but again only one random number will be present

SELECT RAND() AS Random, Name FROM Test

/* EXAMPLE RESULTS

Random                 Name
---------------------- ----
0.0554699428597496     Bob
0.0554699428597496     Mel
0.0554699428597496     Sam
0.0554699428597496     Jim
0.0554699428597496     Sue
0.0554699428597496     Tim

*/

Random Numbers with NEWID

If you need to create a new pseudo-random number for each query row, you can work around the above problem with the use of the NewId function. When executed, NewId creates a globally unique identifier (GUID), known in SQL Server as a UNIQUEIDENTIFIER. If used in a query, the GUID for each row will be different.

To see this in action, run the following query. The results should be similar to those shown in the comment.

SELECT NEWID() AS Random, Name FROM Test

/* EXAMPLE RESULTS

Random                               Name
------------------------------------ ----
E750D6CF-AAEA-4807-89C8-6489B5F06156 Bob
7E7DF68D-0786-4CFB-ADD3-E9A11BBDF9BA Mel
A74B2AF0-BBBC-490B-B439-A3385D92A62D Sam
0A1BA0EA-0542-4119-B8B3-3A5C01AE44BE Jim
2ED6DB3C-9432-46A5-9FF9-305CC496758B Sue
D9671433-4A9F-4011-ADB0-11246FF14635 Tim

*/

Usually when you are creating randomised values you'll want to work with a numeric data type, rather than a GUID. There's no standard conversion from the UNIQUEIDENTIFIER data type to any of the numeric types. However, we can convert to a BINARY value, which can then be converted to a number. A good candidate for the final data type is BIGINT, which holds 64-bit integers. As this data type requires eight bytes of data, the first conversion should be to an eight byte binary value.

The following updated query adds the conversions so that the Random column holds a set of BIGINTs:

SELECT CONVERT(BIGINT,CONVERT(BINARY(8), NEWID())) AS Random, Name FROM Test

/* EXAMPLE RESULTS

Random               Name
-------------------- ----
-7594016697478730431 Bob
7612722008485863494  Mel
-4029374675441831097 Sam
-5991042230082790833 Jim
1520621435107131057  Sue
-4874896925904449981 Tim

*/

The range of the BIGINT data type is very large, making it flexible for situations where the spread of random numbers required is wide. In many cases you'll want a limited range of values. For the final example we'll generate random numbers between zero and nine. This is achieved with two minor changes. Firstly, the use of the Abs function ensures the random number is positive. Next, the modulus operator limits the range of possible values.

The final query is as follows:

SELECT ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % 10 AS Random, Name FROM Test

/* EXAMPLE RESULTS

Random               Name
-------------------- ----
5                    Bob
1                    Mel
8                    Sam
2                    Jim
0                    Sue
0                    Tim

*/

NB: The algorithm used by SQL Server's NewId function almost entirely relies upon randomisation, making it a good candidate for creating pseudo-random numbers. However, as with RAND the function is software-based. This means that if you know the state of the machine it is theoretically possible to predict the next value that will be generated. You should therefore not use this approach where true randomisation is required. For further reading you might be interested to know that NewId generates version 4 GUIDs.