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

Hostinger image

Get n8n VPS hosting 3x cheaper than a cloud solution

Get fast, easy, secure n8n VPS hosting from $4.99/mo at Hostinger. Automate any workflow using a pre-installed n8n application and no-code customization.

Start now

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

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay