How do I generate random number for each row in a TSQL Select

Question

I need a different random number for each row in my table   The following seemingly obvious code uses the same random value for each row   SELECT table name  RAND   magic number  FROM information schema tables    I d like to get an INT or a FLOAT out of this   The rest of the story is I m going to use this random number to create a random date offset from a known date  e g  1-14 days offset from a start date   This is for Microsoft SQL Server 2000

User · Answer

When called multiple times in a single batch, rand() returns the same number.

I'd suggest using convert(varbinary,newid()) as the seed argument:

SELECT table_name, 1.0 + floor(14 * RAND(convert(varbinary, newid()))) magic_number 
FROM information_schema.tables

newid() is guaranteed to return a different value each time it's called, even within the same batch, so using it as a seed will prompt rand() to give a different value each time.

Edited to get a random whole number from 1 to 14.

User · Answer

RAND CHECKSUM NEWID       The above will generate a  pseudo-  random number between 0 and 1  exclusive   If used in a select  because the seed value changes for each row  it will generate a new random number for each row  it is not guaranteed to generate a unique number per row however    Example when combined with an upper limit of 10  produces numbers 1 - 10    CAST RAND CHECKSUM NEWID       10 as INT    1   Transact-SQL Documentation    CAST    https   docs microsoft com en-us sql t-sql functions cast-and-convert-transact-sql RAND    http   msdn microsoft com en-us library ms177610 aspx CHECKSUM    http   msdn microsoft com en-us library ms189788 aspx NEWID    https   docs microsoft com en-us sql t-sql functions newid-transact-sql

User · Answer

Random number generation between 1000 and 9999 inclusive   FLOOR RAND CHECKSUM NEWID      9999-1000 1  1000      1  - to include upper bound values 9999 for previous example

User · Answer

DROP VIEW IF EXISTS vwGetNewNumber      GO     Create View vwGetNewNumber     as     Select CAST RAND CHECKSUM NEWID       62 as INT    1 as NextID       abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ as alpha num       ---------------CTDE GENERATE PUBLIC KEY -----------------     DROP FUNCTION IF EXISTS CTDE GENERATE PUBLIC KEY        GO     create function CTDE GENERATE PUBLIC KEY       RETURNS NVARCHAR 32      AS      BEGIN         DECLARE  private key NVARCHAR 32           set  private key   dbo CTDE GENERATE 32 BIT KEY            return  private key      END      go  ---------------CTDE GENERATE 32 BIT KEY ----------------- DROP FUNCTION IF EXISTS CTDE GENERATE 32 BIT KEY    GO CREATE function CTDE GENERATE 32 BIT KEY   RETURNS NVARCHAR 32  AS  BEGIN     DECLARE  public key NVARCHAR 32       DECLARE  alpha num NVARCHAR 62       DECLARE  start index INT   0      DECLARE  i INT   0      select top 1  alpha num   alpha num from vwGetNewNumber          WHILE  i  lt  32         BEGIN           select top 1  start index   NextID from vwGetNewNumber            set  public key   concat  substring  alpha num  start index 1   public key             set  i    i   1          END      return  public key  END      select dbo CTDE GENERATE PUBLIC KEY   public key

User · Answer

select ABS CAST CAST NEWID   AS VARBINARY  AS INT   as  Randomizer    has always worked for me

User · Answer

If you want to generate a random number between 1 and 14 inclusive  SELECT CONVERT int  RAND      14 - 1    1   OR SELECT ABS CHECKSUM NewId        14 -1    1

User · Answer

Answering the old question  but this answer has not been provided previously  and hopefully this will be useful for someone finding this results through a search engine   With SQL Server 2008  a new function has been introduced  CRYPT GEN RANDOM 8   which uses CryptoAPI to produce a cryptographically strong random number  returned as VARBINARY 8000   Here s the documentation page  https   docs microsoft com en-us sql t-sql functions crypt-gen-random-transact-sql  So to get a random number  you can simply call the function and cast it to the necessary type   select CAST CRYPT GEN RANDOM 8  AS bigint    or to get a float between -1 and  1  you could do something like this   select CAST CRYPT GEN RANDOM 8  AS bigint    1000000000   1000000000 0

User · Answer

If you need to preserve your seed so that it generates the  same  random data every time  you can do the following   1  Create a view that returns select rand    if object id  cr sample randView   is not null begin     drop view cr sample randView end go  create view cr sample randView as select rand   as random number go   2  Create a UDF that selects the value from the view   if object id  cr sample fnPerRowRand   is not null begin     drop function cr sample fnPerRowRand end go  create function cr sample fnPerRowRand   returns float as begin     declare  returnValue float     select  returnValue   random number from cr sample randView     return  returnValue end go   3  Before selecting your data  seed the rand   function  and then use the UDF in your select statement   select rand 200     -- see the rand   function with cte id  as  select row number   over order by object id  from sys all objects  select      id      dbo cr sample fnPerRowRand   from cte where id  lt   1000    -- limit the results to 1000 random numbers

User · Answer

The problem I sometimes have with the selected  Answer  is that the distribution isn t always even  If you need a very even distribution of random 1 - 14 among lots of rows  you can do something like this  my database has 511 tables  so this works  If you have less rows than you do random number span  this does not work well    SELECT table name  ntile 14  over order by newId    randomNumber  FROM information schema tables   This kind of does the opposite of normal random solutions in the sense that it keeps the numbers sequenced and randomizes the other column   Remember  I have 511 tables in my database  which is pertinent only b c we re selecting from the information schema   If I take the previous query and put it into a temp table  X  and then run this query on the resulting data   select randomNumber  count    ct from  X group by randomNumber   I get this result  showing me that my random number is VERY evenly distributed among the many rows

User · Answer

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

User · Answer

try using a seed value in the RAND seedInt    RAND   will only execute once per statement that is why you see the same number each time

User · Answer

Do you have an integer value in each row that you could pass as a seed to the RAND function   To get an integer between 1 and 14 I believe this would work   FLOOR  RAND  lt yourseed gt     14    1

User · Answer

The Rand   function will generate the same random number  if used in a table SELECT query  Same applies if you use a seed to the Rand function  An alternative way to do it  is using this   SELECT ABS CAST CAST NEWID   AS VARBINARY  AS INT   AS  RandomNumber    Got the information from here  which explains the problem very well

User · Answer

You would need to call RAND   for each row  Here is a good example  https   web archive org web 20090216200320 http   dotnet org za calmyourself archive 2007 04 13 sql-rand-trap-same-value-per-row aspx

User · Answer

Update my table set my field   CEILING  RAND CAST NEWID   AS varbinary     10     Number between 1 and 10

User · Answer

select round rand checksum newid      10  20 2    Here the random number will come in between 20 and 30  round will give two decimal place maximum   If you want negative numbers you can do it with    select round rand checksum newid      10 -60 2    Then the min value will be -60 and max will be -50

User · Answer

Use newid    select newid     or possibly this  select binary checksum newid

User · Answer

Try this   SELECT RAND convert varbinary  newid      b-a  a magic number    Where a is the lower number and b is the upper number

User · Answer

If you don t need it to be an integer  but any random unique identifier  you can use newid    SELECT table name  newid   magic number  FROM information schema tables

User · Answer

It s as easy as   DECLARE  rv FLOAT  SELECT  rv   rand      And this will put a random number between 0-99 into a table   CREATE TABLE R       Number int    DECLARE  rv FLOAT  SELECT  rv   rand     INSERT INTO dbo R  Number      values   rv   100     SELECT   FROM R

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

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2000