Generating random numbers within a specific range is a common requirement in SQL — whether you’re building test datasets, randomizing user experiences, or simply experimenting with different data scenarios.
Today, we’ll walk through various techniques to generate random numbers between 1 and 100 in SQL, from basic methods to more advanced, production-ready approaches.
This guide is perfect for both SQL beginners and experienced database developers looking to add randomness to their queries!
Method 1: Using the RAND() Function
The simplest way to generate a random number in SQL is by using the RAND() function.RAND() returns a floating-point value between 0 and 1. To scale it up to a number between 1 and 100, you multiply it by 100 and round the result.
Example:
1 2 3 | SELECT ROUND(RAND() * 100) AS RandomNumber; |
🔹 This method is quick and works perfectly for single random values.
Method 2: Assign Random Numbers to Multiple Rows
If you’re working with a table (like a list of students) and want each row to have a random number, combine RAND() with ORDER BY.
Example:
1 2 3 4 5 | SELECT StudentName, ROUND(RAND() * 100) AS RandomNumber FROM Students ORDER BY RandomNumber; |
🔹 Each student gets a randomly assigned number, and the results are sorted by that random value.
Method 3: Using NEWID() for Enhanced Randomness (SQL Server)
When you need better randomness for each row, especially in SQL Server, you can use the NEWID() function along with CHECKSUM().
Example (SQL Server):
1 2 3 4 | SELECT StudentName, ABS(CHECKSUM(NEWID()) % 100) + 1 AS RandomNumber FROM Students; |
✅ NEWID() ensures that each random number is seeded uniquely per row, making it truly unpredictable.
Method 4: Generating Random Numbers Across Joined Tables
In complex queries involving multiple tables, you can generate random numbers separately for each entity.
Example:
1 2 3 4 5 6 | SELECT Students.StudentName, ROUND(RAND() * 100) AS StudentRandom, Courses.CourseName, ROUND(RAND() * 100) AS CourseRandom FROM Students JOIN Courses ON Students.StudentID = Courses.StudentID; |
🔹 Here, each student and course gets its own random number between 1 and 100.
Method 5: Using CHECKSUM on Specific Columns
Another creative technique is to generate random numbers based on the CHECKSUM() of unique columns like StudentID.
Example:
1 2 3 4 | SELECT StudentName, ABS(CHECKSUM(StudentID) % 100) + 1 AS RandomNumber FROM Students; |
🔹 This ensures that the random number is based on a unique attribute, maintaining some degree of repeatability.
Method 6: Using ROW_NUMBER() for Ordered Random Numbers
If you want random numbers but also maintain a specific order (for example, alphabetically by student name), you can use ROW_NUMBER().
Example:
1 2 3 4 | SELECT StudentName, ROW_NUMBER() OVER (ORDER BY StudentName) AS RandomNumber FROM Students; |
🔹 Although not truly random, this is useful when you need sequential but seemingly random identifiers.
Conclusion
Generating random numbers between 1 and 100 in SQL is not only simple but also incredibly versatile depending on your specific needs:
- For basic randomness: use
RAND(). - For better uniqueness: use
NEWID()andCHECKSUM(). - For large datasets: consider combining randomness with joins or mathematical functions.
By mastering these techniques, you’ll be better equipped to build dynamic, testable, and real-world-ready SQL queries.
Happy coding! 🎯
