sql server - How do I generate random number for each row in a TSQL Select? -

I need a different random number for each line in my table. The apparent code that appears below uses a similar random value for each line.

  SELECT table_name, RAND () magic_number FROM info_schema.tables  

I like to take out an int or floyat. The rest of the story is going to use random numbers to create random date offsets from a known date, e.g. Offset offset 1-14 days from the initial date.

This is for Microsoft SQL Server 2000.

In short, the following code is a randomized number between 0 and 13 with a normalized distribution To change your category, change the number at the end of the expression:

  ABS (CHECKSUM (NewId ())% 14  

Be extra careful if you need a category that contains both positive and negative numbers, if you do it wrong, it is possible to double count the number 0.

A small warning for math nuts in the room: There is a slight bias in this code. CHECKSUM () The number of results that are normalized in the whole range of SQL int datatype, or at least I can show editor (test). However, there will be some bias when Chacsam () will produce a number at the top end of that range, at any time before the maximum maximum integer and the last exact number (maximum 14 digits) of your desired category size, Preferred in the rest of the part, which can not be produced 14. Multivariate of the previous time

For example, imagine that the whole range of int type is only 19. 19. The largest possible integer you can keep. When the results of checksum () 14-19, these results are 0-5 compliant; those numbers will be allowed more than 6-13 heavily , because the cheksam () There is a possibility. It's easy to visually display it down to the entire possible result for our hypothetical integer category:

You can see here that there are more possibilities to generate some numbers than others: Bias is Venus , The actual category of int type lot is large ... so much that in most cases, Vigilance is almost inexplicable. However, if you ever do so for serious security code yourself then it is something to be aware of.