Generate random int value from 3 to 6

by

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).
For example:
Select top 100 Rand(),* from tblExample

Would return the same random value for all 100 rows.

While:
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:

  • NEWID generates random string (for each row in return)
  • CHECKSUM takes 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)
  • ABS changes negative results to positive
  • then add one to the result to eliminate 0 results (to simulate a dice roll)

More Related Contents:

  1. How to delete 0 values but not blanks from a column in SQL Server 2008 programming?
  2. Unpivot with column name
  3. How can I delete using INNER JOIN with SQL Server?
  4. What this query does to create comma delimited list SQL Server?
  5. SQL Server – Return value after INSERT
  6. Convert varchar into datetime in SQL Server
  7. sql query to return differences between two tables
  8. Check if a row exists, otherwise insert
  9. How to select the last record of a table in SQL?
  10. How to create table using select query in SQL Server?
  11. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  12. How to write UPDATE SQL with Table alias in SQL Server 2008?
  13. SQL Server 2008- Get table constraints
  14. SQL SERVER: Get total days between two dates
  15. Restrict an SQL Server connection to a specific IP address
  16. Execute Dynamic Query with go in sql
  17. TransactSQL to run another TransactSQL script
  18. How can I get a list of element names from an XML value in SQL Server
  19. Why are logical reads for windowed aggregate functions so high?
  20. Store multiple bit values in a single table column
  21. How to rename something in SQL Server that has square brackets in the name?
  22. Comma Separated values with SQL Query
  23. how to separate string into different columns?
  24. Improve SQL Server query performance on large tables
  25. Using IF ELSE statement based on Count to execute different Insert statements
  26. Backup a single table with its data from a database in sql server 2008
  27. COALESCE Function in TSQL
  28. Try_Convert for SQL Server 2008 R2
  29. Display next event date
  30. Sql query for tree table

Leave a Comment