Using TABLESAMPLE with a WHERE clause

Question

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.

Answer 1

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.