SQL-Quick tip #4 - Random Int for each row

Sql Server tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Random int for each row

If you ever need a random number for each of the rows in your query result then you might think of the RAND() TSql function, but it turns out that this function will return the same value for every row in the result set. The trick to generate a random number for each row is to use the NEWID() and from that calculate the CHECKSUM which we then can turn into a positive number and the calculate the modulus with the maximum random number we want.

The code below show how to create a number between 0 and 24 and another example of how to create a number between 15 and 24.

The calculation of the random int for each row is not very efficient, so you should consider using other more efficient method for generating the random numbers if you need it in production code.

DECLARE @names TABLE ([Name] VARCHAR(50))

INSERT INTO @names ([Name]) 
VALUES ('Joe'), ('Bob'), ('Anne'), ('Jane')

-- Generate random int between 0 and 24
DECLARE @maxRandom INT = 25
SELECT [Name], CAST(ABS(CHECKSUM(NEWID())) % @maxRandom AS INT) [Random]
  FROM @names

-- Generate random int between 15 and 24
DECLARE @minRandom INT = 15
SELECT [Name], CAST(ABS(CHECKSUM(NEWID())) % (@maxRandom-@minRandom) AS INT) + @minRandom [Random]
  FROM @names