sql server - Generate unique random numbers using SQL

Question

I have some SQL code which generates random numbers using the following technique:

DECLARE @Random1 INT, @Random2 INT, @Random3 INT, @Random4 INT, @Random5 INT, @Random6 INT, @Upper INT, @Lower INT
---- This will create a random number between 1 and 49
SET @Lower = 1 ---- The lowest random number
SET @Upper = 49; ---- The highest random number


with nums as (
    select @lower as n
    union all
    select nums.n+1
    from nums
    where nums.n < @Upper
   ),
   randnums as 
   (select nums.n, ROW_NUMBER() over (order by newid()) as seqnum
    from nums
   )
select @Random1 = MAX(case when rn.seqnum = 1 then rn.n end),
       @Random2 = MAX(case when rn.seqnum = 2 then rn.n end),
       @Random3 = MAX(case when rn.seqnum = 3 then rn.n end),
       @Random4 = MAX(case when rn.seqnum = 4 then rn.n end),
       @Random5 = MAX(case when rn.seqnum = 5 then rn.n end),
       @Random6 = MAX(case when rn.seqnum = 6 then rn.n end)
from randnums rn;

select @Random1, @Random2, @Random3, @Random4, @Random5, @Random6

My question is how random is this number generation? and is there another way to do this which is more "random".

I am using:

Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64)   Aug 22 2012 19:25:47   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

The problem with most solutions is you'll end up with values like this: 14,29,8,14,27,27 I cannot have duplicate numbers!

Answer 1

I guess you could do something like this much simpler and much easier

DECLARE @Upper INT;
DECLARE @Lower INT;
SET @Lower = 1;     /* -- The lowest random number */
SET @Upper = 49;    /* -- The highest random number */
    
    
SELECT @Lower + CONVERT(INT, (@Upper-@Lower+1)*RAND());

For getting a random number without repetition, this will do the job

WITH CTE 
AS
(
    SELECT  randomNumber, COUNT(1) countOfRandomNumber
    FROM (
    SELECT ABS(CAST(NEWID() AS binary(6)) %49) + 1 randomNumber
    FROM sysobjects
    ) sample
    GROUP BY randomNumber
)
SELECT TOP 5 randomNumber
FROM CTE
ORDER BY newid() 

To set the highest limit, you can replace 49 with your highest limit number.