How do I generate random numbers from a column, without duplicates - SQL Server

Question

I have a Profile table with columns: UserID, Firstname, Lastname

I am trying create a stored procedure which passes in a @UserID parameter, and generates 10 random numbers taken from the UserID column.

The aim of this is to allow one user to send a message to 10 random users (without any duplicates)

The following is what I have so far:

CREATE PROCEDURE [dbo].[Random10]
@UserID INT
AS
DECLARE @MaxID INT, @MinID INT, @RandomID INT, @Index INT

SET @MinID = (SELECT MIN(P.UserID) FROM Profile P)
SET @MaxID = (SELECT MAX(P.UserID) FROM Profile P)
SET @Index = 0

CREATE TABLE #RandomUsers
(
ID INT PRIMARY KEY IDENTITY,
UserID INT
)


WHILE @Index < 10
BEGIN
    SELECT @RandomID = ROUND(((@MaxID - @MinID - 1) * RAND() + @MinID), 0)

    IF (@RandomID <> @UserID)
    BEGIN
        INSERT INTO #RandomUsers VALUES (@RandomID)
        SET @Index = @Index + 1
    END
ELSE
    BEGIN
        SET @Index = @Index
    END
END

SELECT * FROM #RandomUsers

I am passing in the UserID '66' and using a 'WHILE LOOP' and 'RAND()' to generate the 10 numbers. The 'IF' statement is used to eliminate the '@UserID' parameter from appearing in the list (because this would be the user sending the message)

Here is the result:

|ID|UserID|
| 1|    66|
| 2|    80|
| 3|    66|
| 4|    64|
| 5|    14|
| 6|    72|
| 7|    72|
| 8|    81|
| 9|    19|
|10|    56|

As you can see there are some duplicates.

I have tried to add a WHERE clause to eliminate both these problems but I then get a NULL set returned.

Answer 1

You could try using newid():

INSERT INTO #RandomUsers
Select TOP 10 userId From Profile WHERE userId <> @UserID
ORDER BY newid()

This way you get ten different users that are not the one you passed in parameter. But I don't know how you can get really good randomness in SQL Server.

Edit:

I think you can also use tablesample() for this kind of issue, but I don't really know how to use this.

By the way, if a hole appears in the id sequence (like user 34 is deleted), you could still pick 34 with your method, while methods taking a sample from the table directly will still work.