DEV Community

Cover image for SQL-Quick tip #4 - Random Int for each row
Allan Simonsen
Allan Simonsen

Posted on

11 2

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


Enter fullscreen mode Exit fullscreen mode

Sql Server Management Studio screenshot

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (0)

Billboard image

Try REST API Generation for MS SQL Server.

DevOps for Private APIs. With DreamFactory API Generation, you get:

  • Auto-generated live APIs mapped from database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

πŸ‘‹ Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay