Unsticking the Sticky Seed: Overcoming RAND() Limitations in SQL Server 2000
 RAND() function: While SQL Server offers the
RAND()
function to generate random numbers, it has a limitation.RAND()
calculates a random number based on a single seed value. Within a single query, this seed remains constant, resulting in the same random number for every row.
Here's how to achieve random numbers for each row:

NEWID() and CHECKSUM() approach:
 This method utilizes two functions:
NEWID()
andCHECKSUM()
.NEWID()
generates a unique identifier (GUID) for each row.CHECKSUM()
calculates a hash value based on theNEWID()
output.
 We can convert this hash value to a positive integer using techniques like bitwise operations or converting to string and back to integer.
 Finally, we use the modulo operator (%) with the desired maximum random number range to get a random number within that range.
 This method utilizes two functions:

CRYPT_GEN_RANDOM() function (SQL Server 2016 and later):
 This function, introduced in SQL Server 2016, offers a more robust way to generate cryptographically secure random numbers.
 It takes the desired byte length as input and returns a random binary value.
 We can convert this binary value to an integer using
CAST()
to get a random number.
Generating random numbers between 0 and 1:
SELECT
 Generate a random number between 0 and 1 (exclusive)
ABS(CHECKSUM(NEWID())) / (CAST('1' AS BIGINT) << 64)
FROM YourTable;
Explanation:
NEWID()
generates a unique identifier for each row.CHECKSUM()
calculates a hash value based on theNEWID()
output.ABS(CHECKSUM(NEWID()))
ensures the result is positive./ (CAST('1' AS BIGINT) << 64)
divides by the largest possible integer value, effectively converting the hash to a number between 0 and 1 (excluding 1).
Generating random integers between a specific range (e.g., 10 to 30):
SELECT
 Generate a random integer between 10 and 30 (inclusive)
10 + FLOOR(ABS(CHECKSUM(NEWID())) % (30  10 + 1))
FROM YourTable;
 Similar to the previous example, we calculate a random value between 0 and 1.
% (30  10 + 1)
gives us the range of possible numbers (21 in this case).FLOOR()
removes any decimals, leaving an integer.10 + FLOOR(...)
adds 10 to the random integer, shifting the range to 1030.
Remember:
 This method isn't cryptographically secure, so avoid it for sensitive applications.
 You can adjust the calculations to fit your desired random number range.
Using CRYPT_GEN_RANDOM() function:
This function directly generates cryptographically secure random numbers. Here's an example:
SELECT
 Generate a random integer between 10 and 30 (inclusive)
10 + CAST(CRYPT_GEN_RANDOM(4) AS INT) % (31  10 + 1)
FROM YourTable;
Explanation:
CRYPT_GEN_RANDOM(4)
generates 4 bytes of random binary data.CAST(...) AS INT
converts the binary data to an integer. The remaining part (
%
and+ 10
) adjusts the range to 1030 similar to the previous example.
Benefits of CRYPT_GEN_RANDOM():
 More secure: It generates cryptographically strong random numbers, making it suitable for sensitive applications.
 Simpler syntax: The code is more concise compared to the
NEWID()
andCHECKSUM()
approach.
Important Note:
While CRYPT_GEN_RANDOM()
is generally preferred for newer versions, keep in mind that it might not be available in all SQL Server editions. For compatibility across older versions, the NEWID()
and CHECKSUM()
method remains an option.
sqlserver tsql sqlserver2000