CODEWITHSUNDEEP
sql-server
user40780
user40780

Reputation: 1920

SQL Server fast random sampling

I have been looking for methods for fast sampling from sql table and I found this very useful article:

https://www.sisense.com/blog/how-to-sample-rows-in-sql-273x-faster/

The query looks like:

select * 
from users
where id in (select round(random() * 21e6)::integer as id
             from generate_series(1, 110)
             group by id -- Discard duplicates
            )
limit 100

However for SQL Server, I got errors like:

'random' is not a recognized built-in function name.

Do we have some SQL Server counter part of this fast sampling method? Thanks.

I tried edit as:

select top 100 * from users
    where id in (
      select round(RAND() * 21e6) CAST integer as id
      from generate_series(1, 110)
      group by id -- Discard duplicates
    )

still having error:

The round function requires 2 to 3 arguments.

Upvotes: 0

Views: 432

Answers (2)

user40780
user40780

Reputation: 1920

From Sean's comment: TABLESAMPLE . Would be the solution, now it is extremely fast to sample.

Upvotes: 0

Cedersved
Cedersved

Reputation: 1025

For T-SQL it is RAND(), not RANDOM()

Also, apart from the comment regarding having to use TOP(n) instead of limit - you also have to use CAST or CONVERT for the data type conversion. And generate_series is not a built-in function in sql server.

Upvotes: 1

Related Questions