A helpful editor added the ‘Select’ before each statement but the point of this item is that it can generate unique keys for each row in a return, not just one item (For that I would us the Rand() function).
Select top 100 Rand(),* from tblExample
Would return the same random value for all 100 rows.
Select top 100 ABS(CHECKSUM(NEWID()) % 10),* from tblexample
Would return a different random value between 0 and 9 on each row in the return.
So while the select makes it easier to copy and paste, you can copy the logic into a select statement if that is what is required.
This generates a random number between 0-9
SELECT ABS(CHECKSUM(NEWID()) % 10)
1 through 6
SELECT ABS(CHECKSUM(NEWID()) % 6) + 1
3 through 6
SELECT ABS(CHECKSUM(NEWID()) % 4) + 3
Dynamic (Based on Eilert Hjelmeseths Comment – thanks to jiraiya for providing the visual presentation)
SELECT ABS(CHECKSUM(NEWID()) % (@max - @min + 1)) + @min
Updated based on comments:
NEWIDgenerates random string (for each row in return)
CHECKSUMtakes value of string and creates number
- modulus (
%) divides by that number and returns the remainder (meaning max value is one less than the number you use)
ABSchanges negative results to positive
- then add one to the result to eliminate 0 results (to simulate a dice roll)