Sampling Queries

Description

The TABLESAMPLE statement is used to sample the table. It supports the following sampling methods:

  • TABLESAMPLE(x ROWS): Sample the table down to the given number of rows.
  • TABLESAMPLE(x PERCENT): Sample the table down to the given percentage. Note that percentages are defined as a number between 0 and 100.
  • TABLESAMPLE(BUCKET x OUT OF y): Sample the table down to a x out of y fraction.

Note: TABLESAMPLE returns the approximate number of rows or fraction requested.

Syntax

  1. TABLESAMPLE ({ integer_expression | decimal_expression } PERCENT)
  2.     | TABLESAMPLE ( integer_expression ROWS )
  3.     | TABLESAMPLE ( BUCKET integer_expression OUT OF integer_expression )

Examples

  1. SELECT * FROM test;
  2. +--+----+
  3. |id|name|
  4. +--+----+
  5. | 5|Alex|
  6. | 8|Lucy|
  7. | 2|Mary|
  8. | 4|Fred|
  9. | 1|Lisa|
  10. | 9|Eric|
  11. |10|Adam|
  12. | 6|Mark|
  13. | 7|Lily|
  14. | 3|Evan|
  15. +--+----+
  17. SELECT * FROM test TABLESAMPLE (50 PERCENT);
  18. +--+----+
  19. |id|name|
  20. +--+----+
  21. | 5|Alex|
  22. | 2|Mary|
  23. | 4|Fred|
  24. | 9|Eric|
  25. |10|Adam|
  26. | 3|Evan|
  27. +--+----+
  29. SELECT * FROM test TABLESAMPLE (5 ROWS);
  30. +--+----+
  31. |id|name|
  32. +--+----+
  33. | 5|Alex|
  34. | 8|Lucy|
  35. | 2|Mary|
  36. | 4|Fred|
  37. | 1|Lisa|
  38. +--+----+
  40. SELECT * FROM test TABLESAMPLE (BUCKET 4 OUT OF 10);
  41. +--+----+
  42. |id|name|
  43. +--+----+
  44. | 8|Lucy|
  45. | 2|Mary|
  46. | 9|Eric|
  47. | 6|Mark|
  48. +--+----+