Sql Server–Query random N elements from a table with identity PK

 

A simple solution how to query random N records from a database table without iteration on a full table:

 

--- because cannot call rand() in scalar valued function
CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

go

--- returns a randon number between min and max
CREATE FUNCTION RandNumber(@Min int, @Max int)
RETURNS int
AS
BEGIN
RETURN ceiling(@Min + (select RandNumber from vRandNumber) * (@Max-@Min))
END

go


-- selecting 100 random rows from a table
declare @cnt int

select @cnt = MAX(<pk_column_name, sysname, ID>)
from <table_name, sysname, sample_table>



select top <number_of_rows,, 100> *
from <table_name, sysname, sample_table>
where <pk_column_name, sysname, ID> in (
    SELECT dbo.RandNumber(1, @cnt) as rnd
    from <table_name, sysname, sample_table>
)
option (recompile) -- it selects only one row without recompile

 

That’s all.

 

Morzels