Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - RAND() - Generating Random Numbers

By: FYIcenter.com

(Continued from previous topic...)

How To Generate Random Numbers with the RAND() Function?

Random numbers are very useful for generating test data, passwords, or other security related data. SQL Server 2005 offers you the random number generator function RAND in two format:

  • RAND(seed) - Starting a new sequence of random numbers based on the given integer "seed" and returning the first random number in FLOAT(53) from the sequence.
  • RAND() - Returning the next random number in FLOAT(53) from the current sequence. If there has been no current sequence, SQL Server will start a new sequence with a random "seed".

Note that calling RAND(seed) with the same seed will start the same sequence and return the same number. To avoid this repeating pattern, you should always call RAND() without any seed and let the server to randomly pickup a sequence.

The tutorial exercise below shows some good examples on how to generate random numbers:

SELECT RAND(100), RAND(), RAND(); -- new sequence
SELECT RAND(100), RAND(), RAND(); -- same sequence again
SELECT RAND(), RAND(), RAND();
SELECT RAND(), RAND(), RAND();
GO
0.715436657367485  0.28463380767982   0.0131039082850364
0.715436657367485  0.28463380767982   0.0131039082850364
0.28769876521071   0.100505471175005  0.292787286982702
0.868829058415689  0.370366365964781  0.58334760467751

-- Random integer between 0 and 100
SELECT FLOOR(100*RAND());
SELECT FLOOR(100*RAND());
SELECT FLOOR(100*RAND());
SELECT FLOOR(100*RAND());
GO
68
29
20
82

  1. What Is an Expression?
  2. What Are Arithmetic Operators?
  3. What Happens to an Arithmetic Operation with Two Different Data Types?
  4. How To Convert a Numeric Expression from One Data Type to Another?
  5. How To Convert Numeric Expression Data Types by Assignment Operations?
  6. How To Convert Numeric Expression Data Types using the CAST() Function?
  7. How To Convert Numeric Expression Data Types using the CONVERT() Function?
  8. How To Convert Character Strings into Numeric Values?
  9. What Happens When Converting Big Values to Integers?
  10. What Happens When Converting Big Values to NUMERIC Data Types?
  11. What Are the Mathematical Functions Supported by SQL Server 2005?
  12. How To Convert Numeric Values to Integers?
  13. How To Round a Numeric Value To a Specific Precision?
  14. How To Generate Random Numbers with the RAND() Function?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...