Creating Random numbers in SQL Server (RAND() vs NEWID())

    There are multiple ways to generate a random number in SQL Server. You may need to create one random number per row or you may need to generate multiple different random numbers per row. I am going to try to cover all scenarios in this post. Let's start with the RAND() function.

RAND([seed])

      You can use the RAND() function to generate a random number or a random number within a range. seed is an optional expression, Its data type can be tinyint, smallint or int. Repetitive RAND calls with the same seed value returns the same result. RAND function returns float value between 0 and 1.  Let's have some examples.

SELECT RAND()
> 0.713610626184182
SELECT FLOOR(RAND())
> 0
SELECT RAND(2), RAND(2)
> 0.570956802191052           0.570956802191052
SELECT FLOOR(RAND() * 10) + 1
> 6  

       As you can see in the first example, RAND() function returns a float number, I used FLOOR function to return integer in the second example. Third example shows you that, RAND() function returns the same value if you use the same seed value. In the last example select statement returns a random number between 1 and 10.

NEWID()

   NEWID() function returns uniqueidentifier type. You can use the CHECKSUM function which computes a hash value over its arguments. We can pass the NEWID() function as argument. CHECKSUM function returns integer data type. Let's look at how it works.

SELECT NEWID()
> F76AAD7A-806F-471E-8C20-C9791152F6D6
SELECT CHECKSUM(NEWID())
> 2113867895

    As you can see NEWID() function itself does not help us at all. CHECKSUM function helped us to create a random number but we need to keep the returned number under control here. To define a range for returned integer,  I am going to use the modulus operator. Here is an example.

SELECT CHECKSUM(NEWID()) %10
> 8

    Now the returned number should be between -10 and 10. Probably you are not going to care about the values less than 0 so to get rid off the values less than 0, I am going to use the ABS function which returns the absolute value of a number.

SELECT ABS(CHECKSUM(NEWID()) %10)
> 3