get a random and unique id in a table

Today I created a random ID. The random ID must still be primary key what means that it must be unique. This is a code walk-through to solve this problem:

First of all I created a play-ground to make tests (with 4 test-entries).

declare @tbl TABLE(ID int)
insert into @tbl values (3), (7), (9), (10) 

Then I introduced a parameter to define the scope. Here we see 0 as an error case (no more IDs available) and start with ID 1 to @maxValue.

declare @maxValue int = 10

 Then we create a loop to test the development for example 20 times:

declare @i int = 0
while (@i < 20) begin

The idea now is to create a random value (=myRequestID) and to check whether the ID currently in use (if so, it will be copied to targetID).

    declare @myRequestID int = (cast(   (RAND()*(@maxValue-1))    as int) + 1)
    declare @myTargetID int = 0
    if not exists(select * from @tbl where ID = @myRequestID) begin
            set @myTargetID = @myRequestID
            insert into @tbl values (@myTargetID)

if we have already used this value (it exists in @tbl) then we need to search for an ID after the random value. Therefore we can choose all requested entries after the random value which have no next sibling and return the smallest id + 1

    end else begin      
        select @myTargetID = isnull(MIN(id) + 1, 0) from @tbl t
        where id between @myRequestID and (@maxValue - 1)
        and not exists(select * from @tbl innerT where innerT.ID = t.ID + 1)

if the targetID has a value we found a valid entry, else if the value is zero, we have not found an entry after the random value, so we jump over the maxValue border and restart at the beginning. Here we need to consider that the value 1 is a special case which needs to be handled extraordinary. 

        if @myTargetID <> 0 begin
            insert into @tbl values (@myTargetID)
        end else if not exists(select * from @tbl where ID = 1) begin
            set @myTargetID = 1;
            insert into @tbl values (@myTargetID)

Finally if 1 is already in use we can search for an ID hole from the start to the random value

        end else begin
            select @myTargetID = isnull(MIN(id) + 1, 0) from @tbl t
            where id < @myRequestID
            and not exists(select * from @tbl innerT where innerT.ID = t.ID + 1)
            if @myTargetID <> 0 begin
                insert into @tbl values (@myTargetID)
            end
        end

If no value was found in the end there is probably no hole available and we have no value to return.

        if @myTargetID = 0 begin
            print 'no value'
        end
    end

(end of the loop)

    set @i = @i + 1;
end

kr, Daniel