Skip to main content

Datometry Documentation

Enable Azure Synapse TABLESAMPLE

The use_synapse_tablesample_for_sample hint lets you enable the use of the Azure Synapse TABLESAMPLE statistics collection command. Normally, Hyper-Q uses SAMPLE to collect table statistics. However, the use of SAMPLE can be prohibitively slow. For this reason you can enable the use of TABLESAMPLE, which also disables (turns off) SAMPLE when accessing Azure Synapse through Hyper-Q.

TABLESAMPLE allows you to extract a sampling of rows from a table using the FROM clause. The rows retrieved are random and are not in any order. This sampling is based on a percentage of the number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set. The following example illustrates the use of TABLESAMPLE.

SELECT *
FROM table_name
TABLESAMPLE (10 PERCENT)

To learn more about TABLESAMPLE, see:

The syntax for use_synapse_tablesample_for_sample is:

use_synapse_tablesample_for_sample = true | false

where:

Parameter

Description

false

Disables the use of TABLESAMPLE. This is the default behavior.

true

Enables the use of TABLESAMPLE for sampling on Azure Synapse. Using TABLESAMPLE is much faster than the existing Hyper-Q emulation of SAMPLE for large tables, however, it is a non-deterministic sampling and the results may vary each time you sample the same table.

Supported Hint Scope Level

You can use the use_synapse_tablesample_for_sample hint as a statement, request, or session level hint. Hints at the statement scope apply to the entire SQL statement to which they are associated. You cannot use the use_synapse_tablesample_for_sample hint at the application scope level. To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.

Example 35. Specify that Hyper-Q Enable TABLESAMPLE

You can specify use_synapse_tablesample_for_sample as a query hint at either the statement or session level. This example specifies that the hint be used at the for session level within a SELECT FROM statement.

SELECT *
-- @dtm_use_synapse_tablesample_for_sample=true for session
FROM table_name