Where to order a result set randomly - in database

Question

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?

Answer 1

You can use pseudorandom numbers in TSQL to generate a reproducible list of randomized values.

Removing the comment delimiter from the calculation of @Seed will cause the sequence to vary for each new value of @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