SELECT TOP(100) T.Имя, T.значение
FROM T JOIN master.dbo.spt_values V ON V.type='P' AND V.number<100*T.[вероятность выпадения]
ORDER BY NEWID();

declare @t table (
    id              int                 not null    identity (1,1)
    , name          varchar (100)       not null
    , probability   int                 not null
    , hits_count    int                 not null
    , primary key (
        id
    )
)

insert @t (
    name
    , probability
    , hits_count
)    
            select  '1%', 1, 0
union all   select  '10% - 1', 10, 0            
union all   select  '10% - 2', 10, 0
union all   select  '79%', 79, 0

declare
    @i      int = 0
    , @roll float
    
while @i < 1000 begin
    set @i += 1
    set @roll = rand() * 100
    
    update a set
        a.hits_count += 1
    from    @t  a
    where
        a.id = (
            select
                a.id
            from (
                select
                   a.*
                   , roll_till = a.roll_from + a.probability 
                from (
                    select
                        a.id
                        , a.name
                        , a.probability
                        , roll_from = isnull ( (select sum(x.probability) from @t x where a.probability < x.probability or (a.probability = x.probability and a.id < x.id) ), 0 )
                    from    @t a   
                ) a        
            ) a
            where
                @roll >= a.roll_from
                and @roll < a.roll_till        
        )                
end

select * from @t

declare @testtable table (name nvarchar(10), chance float(2), value int);

insert into @testtable values ('Значение 1', 0.3, 1), ('Значение 2', 0.2, 2), ('Значение 3', 0.5, 3);

with table1 as (
  select t.name, cast(sum(t1.chance) as float(2)) as p_total, t.value
         from @testtable t
              join @testtable t1 on (t1.value <= t.value)
   group by t.name, t.value),
     table2 as (
  select ABS(CAST(NEWID() AS binary(8)) %100/*maximum*/)/cast(100.00 as float(2)) as num
         from master..spt_values V 
   where V.type='P' AND V.number<1000),
     result as (
  select num, (select top 1 name 
                 from table1 
                where p_total > table2.num 
                order by value asc) as name
    from table2)
select name, COUNT(*) 
from result
group by name