How do I generate a random number for each row in a T-SQL select?

by

Take a look at SQL Server – Set based random numbers which has a very detailed explanation.

To summarize, the following code generates a random number between 0 and 13 inclusive with a uniform distribution:

ABS(CHECKSUM(NewId())) % 14

To change your range, just change the number at the end of the expression. Be extra careful if you need a range that includes both positive and negative numbers. If you do it wrong, it’s possible to double-count the number 0.

A small warning for the math nuts in the room: there is a very slight bias in this code. CHECKSUM() results in numbers that are uniform across the entire range of the sql Int datatype, or at least as near so as my (the editor) testing can show. However, there will be some bias when CHECKSUM() produces a number at the very top end of that range. Any time you get a number between the maximum possible integer and the last exact multiple of the size of your desired range (14 in this case) before that maximum integer, those results are favored over the remaining portion of your range that cannot be produced from that last multiple of 14.

As an example, imagine the entire range of the Int type is only 19. 19 is the largest possible integer you can hold. When CHECKSUM() results in 14-19, these correspond to results 0-5. Those numbers would be heavily favored over 6-13, because CHECKSUM() is twice as likely to generate them. It’s easier to demonstrate this visually. Below is the entire possible set of results for our imaginary integer range:

Checksum Integer: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Range Result:     0 1 2 3 4 5 6 7 8 9 10 11 12 13  0  1  2  3  4  5

You can see here that there are more chances to produce some numbers than others: bias. Thankfully, the actual range of the Int type is much larger… so much so that in most cases the bias is nearly undetectable. However, it is something to be aware of if you ever find yourself doing this for serious security code.

More Related Contents:

  1. Check if table exists in SQL Server
  2. Find index of last occurrence of a sub-string using T-SQL
  3. How can I insert binary file data into a binary SQL field using a simple insert statement?
  4. INSERT INTO @TABLE EXEC @query with SQL Server 2000
  5. Comma separated results in SQL
  6. T-SQL stored procedure that accepts multiple Id values
  7. When should I use semicolons in SQL Server?
  8. How to get Time from DateTime format in SQL?
  9. Parse JSON in TSQL
  10. Get size of all tables in database
  11. Fuzzy matching using T-SQL
  12. Why use a READ UNCOMMITTED isolation level?
  13. Replace duplicate spaces with a single space in T-SQL
  14. SQL Server: Isolation level leaks across pooled connections
  15. How do I use the CONCAT function in SQL Server 2008 R2?
  16. How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?
  17. How do I exclude Weekend days in a SQL Server query?
  18. Pivot using SQL Server 2000
  19. EXEC sp_executesql with multiple parameters
  20. Can you call a webservice from TSQL code?
  21. How to add custom attributes to SQL connection string?
  22. Joining multiple tables returns NULL value
  23. Which join syntax is better?
  24. Compute MD5 hash of a UTF8 string
  25. LPAD in SQL Server 2008
  26. How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
  27. How do I remove non-breaking spaces from a column in SQL server?
  28. How to set value to variable using ‘execute’ in t-sql?
  29. Update SQL with consecutive numbering
  30. Count number of records returned by group by

Leave a Comment