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

1到10之间的数字。

如果你需要保存你的种子，以便它每次生成“相同”的随机数据，你可以做以下事情:

1. 创建一个返回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. 创建一个从视图中选择值的UDF。

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.在选择数据之前，输入rand()函数，然后在选择语句中使用UDF。

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

回答老问题，但是这个答案之前没有提供，希望这对通过搜索引擎找到这个结果的人有用。

在SQL Server 2008中，引入了一个新函数CRYPT_GEN_RANDOM(8)，它使用CryptoAPI生成一个加密强随机数，返回为VARBINARY(8000)。这里是文档页面:https://learn.microsoft.com/en-us/sql/t-sql/functions/crypt-gen-random-transact-sql

因此，为了获得一个随机数，你可以简单地调用函数并将其强制转换为必要的类型:

select CAST(CRYPT_GEN_RANDOM(8) AS bigint)

或者要获得一个介于-1和+1之间的浮点数，你可以这样做:

select CAST(CRYPT_GEN_RANDOM(8) AS bigint) % 1000000000 / 1000000000.0

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

一直对我有效吗

    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;

生成1000到9999之间的随机数，包括:

FLOOR(RAND(CHECKSUM(NEWID()))*(9999-1000+1)+1000)

"+1" -包含上限值(上例为9999)