TABLESAMPLE
TABLESAMPLE is an SQL operator used to retrieve a random sample of rows from a table. It enables efficient selection of a limited number of rows without scanning full table. It can improve performance when dealing with large volumes of data by providing an approximation of the full data set.
Example
SELECT * FROM employees TABLESAMPLE BERNOULLI(5);Output
emp_id | name | department--------+--------+------------ 1001 | John | Sales 2002 | Alice | Marketing 3003 | Bob | ITExplanation
The TABLESAMPLE keyword uses the Bernoulli method to return 5% of the rows in the ‘employees’ table. The result can vary, as the Bernoulli method employs randomness in row selection.
Example
SELECT * FROM Sales.SalesOrderDetailTABLESAMPLE (10 PERCENT)Output
SalesOrderID SalesOrderDetailID CarrierTrackingNumber OrderQty ProductID ...43659 517 4911-403C-98 1 77643659 518 4911-403C-98 3 777...Explanation
The TABLESAMPLE clause is used to return a sample percentage of rows from a table. In the example above, we are querying the SalesOrderDetail table from the Sales schema and we are asking for 10% of the rows to be returned. The output shown is a subset of the full table, representing approximately 10% of the entire data set. Please note that the actual number of rows returned by TABLESAMPLE can vary because the function is based on statistical methods.
Example
SELECT *FROM employeesTABLESAMPLE (10);Output
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID ||-------------|------------|-----------|-------|--------------|-----------|--------|--------|----------------|------------|---------------|| 200 | Jennifer | Whalen | JWHALEN| 515.123.4444 | 17-JUN-03 | AD_ASST| 4400 | NULL | 101 | 10 || 202 | Pat | Fay | PFAY | 603.123.6666 | 17-AUG-05 | MK_REP | 6000 | NULL | 201 | 20 |Explanation
The TABLESAMPLE clause is used to perform a random sample of the table data. In the provided example, 10% of the rows from ‘employees’ table are selected randomly. The output shows the attributes of the selected employees. Results may vary because of randomness.