Data source will contain approx. 5000 records.
I know that it is possible to order data randomly with orderby newid(). But data to be sorted should be paged. That means page n can not contain records from previous pages (n-1, n-2 etc). Now I think I have 2 ways to go: ordering in database, or in memory (because 5000 records is small enough to hold it in in-memory cache).
For option 1 (sorting in db) I am not sure if it's possible because data should be paged. Is it possible if yes how? For option 2, are there any good algorithms (good is performant and highly random sorter) to order data in memory ? How would you go in this scenario: ordering in memory or in database, why?
您可以使用伪随机数在T-SQL，生成随机值的可重复的名单。
从计算删除注释定界符@Seed将导致序列为的每一个新值而变化@LastLogin 。
-- Some user specific value that does not change.
declare @UserId as Int = 42
-- Some user specific value that changes as often as you want the order to change for a user.
declare @LastLogin as DateTime = SysDateTime()
-- Paging parameters.
declare @PageSize as Int = 10
declare @PageNumber as Int = 2
select @UserId as Seed, @UserId + DatePart( ms, @LastLogin ) as AlternativeSeed, @LastLogin as LastLogin
declare @Seed as Int = @UserId -- + DatePart( ms, @LastLogin )
; with Numbers ( Number, PseudorandomNumber ) as (
-- Select the "first" row from your data.
select 1, Rand( @Seed )
union all
-- Add the "next" row from your data.
select Number + 1, Rand( 1000000 * PseudorandomNumber )
from Numbers
where Number < 100
),
-- Add row numbers to the previous rowset to allow paging.
NumbersWithRowNumber as (
select *, Row_Number() over ( order by PseudorandomNumber ) as RowNumber
from Numbers
)
-- Select the requested page of data.
select *
from NumbersWithRowNumber
where RowNumber between @PageSize * ( @PageNumber - 1 ) + 1 and @PageSize * @PageNumber
order by RowNumber
