Using TABLESAMPLE with a WHERE clause

Pieter Wessels 21 Reputation points
2021-02-11T11:42:45.63+00:00

My SQL:

SELECT DISTINCT event_id, event_date
FROM my_table TABLESAMPLE(30 PERCENT) REPEATABLE(1)
WHERE system_id='ABC';

Is the sample drawn from the table before, or after the WHERE clause is enacted?

In other words - will my sample be 30% of records with system_id = ABC, or will it be the records with system_id= ABC drawn from a random selection of 30% of the data? This is important, because the data is not evenly distributed : system_id = ABC could be so rare that I might not encounter it in 30% of the data.

Azure SQL Database
0 comments No comments
{count} votes

Accepted answer
  1. Anurag Sharma 17,581 Reputation points
    2021-02-12T04:48:45.75+00:00

    hi @Pieter Wessels , welcome to Microsoft Q&A forum.

    The 'where' clause is applicable after the sampling is done. So if we run the above query, firstly random selection will happen and then system_id = 'abc' will be applied thereafter.

    Please refer to below links for same:
    https://www.reddit.com/r/SQLServer/comments/5wjdoy/tablesample_and_where/
    https://dba.stackexchange.com/questions/258271/perform-tablesample-with-where-clause-in-postgresql

    ----------

    Please mark it accept answer if it helps.


0 additional answers

Sort by: Most helpful