问按性能随机排序

EN

--
-- First, create a table with some dummy data to select from
-- 

DROP TABLE IF EXISTS MainTable
CREATE TABLE MainTable(
    Id int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50) NULL,
    [Content] text NULL
)
GO

DECLARE @I INT = 0
WHILE @I < 40
BEGIN
    INSERT INTO MainTable VALUES('Foo', 'bar')
    SET @I=@I+1
END
UPDATE MainTable SET [Name] = [Name] + CAST(Id as nvarchar(50))

-- Create a gap in IDs at the end
DELETE FROM MainTable
    WHERE ID < 10

-- Create a gap in IDs in the middle
DELETE FROM MainTable
    WHERE ID >= 20 AND ID < 30

-- We now have our "source" data we want to select random rows from



--
-- Then we select random data from our table
-- 

-- Get the interval of values to pick random values from
DECLARE @MaxId int
SELECT @MaxId = MAX(Id) FROM MainTable

DECLARE @MinId int
SELECT @MinId = MIN(Id) FROM MainTable

DECLARE @RandomId int
DECLARE @NumberOfIdsTofind int = 10

-- Make temp table to insert ids from
DROP TABLE IF EXISTS #Ids
CREATE TABLE #Ids (Id int)

WHILE (@NumberOfIdsTofind > 0)
BEGIN
    SET @RandomId = ROUND(((@MaxId - @MinId -1) * RAND() + @MinId), 0)
    -- Verify that the random ID is a real id in the main table
    IF EXISTS (SELECT Id FROM MainTable WHERE Id = @RandomId)
    BEGIN
        -- Verify that the random ID has not already been inserted
        IF NOT EXISTS (SELECT Id FROM #Ids WHERE Id = @RandomId)
        BEGIN
            -- It's a valid, new ID, add it to the list.
            INSERT INTO #Ids VALUES (@RandomId)
            SET @NumberOfIdsTofind = @NumberOfIdsTofind - 1;
        END
    END
END

-- Select the random rows of data by joining the main table with our random Ids   
SELECT MainTable.* FROM MainTable
INNER JOIN #Ids ON #Ids.Id = MainTable.Id