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

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.

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.

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.

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.

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(<yourseed>) * 14) + 1

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

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

select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) as [Randomizer]

has always worked for me

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)

Try this:

SELECT RAND(convert(varbinary, newid()))*(b-a)+a magic_number 

Where a is the lower number and b is the upper number

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

Update my_table set my_field = CEILING((RAND(CAST(NEWID() AS varbinary)) * 10))

Number between 1 and 10.

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

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:

1. CAST(): https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
2. RAND(): http://msdn.microsoft.com/en-us/library/ms177610.aspx
3. CHECKSUM(): http://msdn.microsoft.com/en-us/library/ms189788.aspx
4. NEWID(): https://docs.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql

    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 < 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;

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

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:

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.

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 <= 1000    -- limit the results to 1000 random numbers

Use newid()

select newid()

or possibly this

select binary_checksum(newid())