问按性能随机排序

EN

CREATE TABLE [dbo].[TestTable]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Col1] [nvarchar](100) NOT NULL,
    [Col2] [nvarchar](38) NOT NULL,
    [Col3] [datetime] NULL,
    [Col4] [nvarchar](50) NULL,
    [Col5] [int] NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
 (
    [ID] ASC
 )
)

GO

CREATE NONCLUSTERED INDEX [IX_TestTable_Col5] ON [dbo].[TestTable] 
(
    [Col5] ASC
)

select top 10
  T.ID,
  T.Col1,
  T.Col2,
  T.Col3,
  T.Col5,
  T.Col5
from TestTable as T
order by newid()

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 13 ms.

(10 row(s) affected)
Table 'TestTable'. Scan count 1, logical reads 12492, physical reads 14, read-ahead reads 6437, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 859 ms,  elapsed time = 1700 ms.

select 
  T.ID,
  T.Col1,
  T.Col2,
  T.Col3,
  T.Col5,
  T.Col5
from TestTable as T
  inner join (select top 10 ID
              from TestTable
              order by newid()) as C
    on T.ID = C.ID

SQL Server parse and compile time: 
   CPU time = 125 ms, elapsed time = 183 ms.

(10 row(s) affected)
Table 'TestTable'. Scan count 1, logical reads 1291, physical reads 10, read-ahead reads 399, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 516 ms,  elapsed time = 706 ms.

declare @factor int
select @factor=8000/avg_record_size_in_bytes from sys.dm_db_index_physical_stats(db_id(), object_id('sample'), null, null, 'detailed') where index_level = 0
declare @numRows int = 10
declare @sampledRows int = @factor * @numRows
declare @stmt nvarchar(max) = N'select top (@numRows) * from sample tablesample (' + convert(varchar(32), @sampledRows) + ' rows) order by checksum(newid())'
exec sp_executesql @stmt, N'@numRows int', @numRows

--
-- 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