Cf1RtLpH

1Select top(10) field1,field2 .. fieldn
2from Table1
3order by checksum(newid())
4	
5CREATE TABLE [dbo].[TestTable]
6(
7    [ID] [int] IDENTITY(1,1) NOT NULL,
8    [Col1] [nvarchar](100) NOT NULL,
9    [Col2] [nvarchar](38) NOT NULL,
10    [Col3] [datetime] NULL,
11    [Col4] [nvarchar](50) NULL,
12    [Col5] [int] NULL,
13 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
14 (
15    [ID] ASC
16 )
17)
18
19GO
20
21CREATE NONCLUSTERED INDEX [IX_TestTable_Col5] ON [dbo].[TestTable] 
22(
23    [Col5] ASC
24)
25	
26select top 10
27  T.ID,
28  T.Col1,
29  T.Col2,
30  T.Col3,
31  T.Col5,
32  T.Col5
33from TestTable as T
34order by newid()
35	
36SQL Server parse and compile time: 
37   CPU time = 0 ms, elapsed time = 13 ms.
38
39(10 row(s) affected)
40Table '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.
41
42 SQL Server Execution Times:
43   CPU time = 859 ms,  elapsed time = 1700 ms.
44	
45select 
46  T.ID,
47  T.Col1,
48  T.Col2,
49  T.Col3,
50  T.Col5,
51  T.Col5
52from TestTable as T
53  inner join (select top 10 ID
54              from TestTable
55              order by newid()) as C
56    on T.ID = C.ID
57	
58SQL Server parse and compile time: 
59   CPU time = 125 ms, elapsed time = 183 ms.
60
61(10 row(s) affected)
62Table '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.
63
64 SQL Server Execution Times:
65   CPU time = 516 ms,  elapsed time = 706 ms.
66	
67declare @factor int
68select @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
69declare @numRows int = 10
70declare @sampledRows int = @factor * @numRows
71declare @stmt nvarchar(max) = N'select top (@numRows) * from sample tablesample (' + convert(varchar(32), @sampledRows) + ' rows) order by checksum(newid())'
72exec sp_executesql @stmt, N'@numRows int', @numRows
73	
74--
75-- First, create a table with some dummy data to select from
76-- 
77
78DROP TABLE IF EXISTS MainTable
79CREATE TABLE MainTable(
80    Id int IDENTITY(1,1) NOT NULL,
81    [Name] nvarchar(50) NULL,
82    [Content] text NULL
83)
84GO
85
86DECLARE @I INT = 0
87WHILE @I < 40
88BEGIN
89    INSERT INTO MainTable VALUES('Foo', 'bar')
90    SET @I=@I+1
91END
92UPDATE MainTable SET [Name] = [Name] + CAST(Id as nvarchar(50))
93
94-- Create a gap in IDs at the end
95DELETE FROM MainTable
96    WHERE ID < 10
97
98-- Create a gap in IDs in the middle
99DELETE FROM MainTable
100    WHERE ID >= 20 AND ID < 30
101
102-- We now have our "source" data we want to select random rows from
103
104
105
106--
107-- Then we select random data from our table
108-- 
109
110-- Get the interval of values to pick random values from
111DECLARE @MaxId int
112SELECT @MaxId = MAX(Id) FROM MainTable
113
114DECLARE @MinId int
115SELECT @MinId = MIN(Id) FROM MainTable
116
117DECLARE @RandomId int
118DECLARE @NumberOfIdsTofind int = 10
119
120-- Make temp table to insert ids from
121DROP TABLE IF EXISTS #Ids
122CREATE TABLE #Ids (Id int)
123
124WHILE (@NumberOfIdsTofind > 0)
125BEGIN
126    SET @RandomId = ROUND(((@MaxId - @MinId -1) * RAND() + @MinId), 0)
127    -- Verify that the random ID is a real id in the main table
128    IF EXISTS (SELECT Id FROM MainTable WHERE Id = @RandomId)
129    BEGIN
130        -- Verify that the random ID has not already been inserted
131        IF NOT EXISTS (SELECT Id FROM #Ids WHERE Id = @RandomId)
132        BEGIN
133            -- It's a valid, new ID, add it to the list.
134            INSERT INTO #Ids VALUES (@RandomId)
135            SET @NumberOfIdsTofind = @NumberOfIdsTofind - 1;
136        END
137    END
138END
139
140-- Select the random rows of data by joining the main table with our random Ids   
141SELECT MainTable.* FROM MainTable
142INNER JOIN #Ids ON #Ids.Id = MainTable.Id