How To Generate Random Numbers with the RAND() Function in MS SQL Server?
Submitted by: AdministratorRandom 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
Submitted by: Administrator
* 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
Submitted by: Administrator
-- 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
Submitted by: Administrator
SELECT FLOOR(100*RAND());
SELECT FLOOR(100*RAND());
SELECT FLOOR(100*RAND());
SELECT FLOOR(100*RAND());
GO
68
29
20
82
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Select Some Specific Rows from a Table in MS SQL Server? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | Does Index Slows Down INSERT Statements? |
☺ | How To Configure ODBC DSN with Different Port Numbers? |
☺ | How To Use Subqueries with the EXISTS Operators in MS SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |