Datometry Documentation

You add Hyper-Q hints inline with the SQL statement to which you want to apply it.

SELECT
    --@dtm_hint_name=parameter_value 
    * FROM table_name;

--@dtm_hint_name=parameter_value
SELECT * FROM table_name;

SELECT * FROM table_name;
--@dtm_hint_name=parameter_value

SELECT * FROM table_name
--@dtm_hint_name=parameter_value 
;

When adding a hint to a view, you must add the hint to the view query.

CREATE TABLE table_name (i int);
CREATE VIEW view_name as (SELECT * FROM table_name);

SELECT
    --@dtm_hint_name=parameter_value 
    * FROM view_name;

Similarly, if you are applying a hint to a recursive query add the hint to the view query.

CREATE TABLE schema_name.recursive_view_base_table (i int);
CREATE RECURSIVE VIEW recursive_view_name (i, DEPTH) as
(
    SEL i, 0 as DEPTH
    FROM schema_name.recursive_view_base_table
    UNION ALL
    SEL i, DEPTH+1 FROM 
    recview2
    WHERE DEPTH < 3
);

SELECT * 
    --@dtm_suggest_table_for_temp_table_distr_cols=test_schema.recursive_view_base_table
    FROM recursive_view_name;

CREATE TABLE table_name (i int);
CREATE VIEW view_name as (
SELECT * 
     --@dtm_hint_name=parameter_value -- Will fail.
      FROM table_name);

SELECT * FROM view_name;

Hyper-Q does not support scope-overrides when using the EXEC command to execute a stored procedure or a SQL macro.

CREATE TABLE table_name (i int);
CREATE MACRO macro_name as (
    --@dtm_hint_name=parameter_value  
    SELECT * FROM table_name
    ; SELECT * FROM table_name;
);

EXEC macro_name;

CREATE TABLE table_name (i int);
CREATE MACRO macro_name as (
    SELECT * FROM table_name
    ; SELECT * FROM table_name;  
);

--@dtm_hint_name=parameter_value  -- Will fail.
EXEC macro_name;

When adding a hint to the body of a DDL statement, add the hint near the SELECT statement of the query.

CREATE TABLE table_name (i int);
CREATE MACRO macro_name as (
    --@dtm_hint_name=parameter_value
    SELECT * FROM table_name
    ; SELECT * FROM table_name;
);

EXEC macro_name;

CREATE TABLE table_name (i int);
CREATE MACRO 
    --@dtm_hint_name=parameter_value. -- Will fail.
    macro_name as (
    SELECT * FROM table_name
    ; SELECT * FROM table_name;
);

EXEC macro_name;

Many Hyper-Q SQL hints can be set in the Hyper-Q configuration file (dtm.ini). When specified in the dtm.ini configuration file, the hint applies to all SQL statements processed through Hyper-Q. To learn more, see Hyper-Q Configuration for Azure Synapse.

Hints specified at higher level scope yield to hints specified at a lower level scope. Hints specified at a lower level apply to all scopes at a higher level.

The precedence for Hyper-Q hints is: query → request → session → application → default behavior

For example, if a hint is specified at the application (or gateway) scope, and a query has the same hint with a different value specified, the hint on the query (statement level) takes precedence.

Statement Level Hints

If you do not apply a scope modifier to a SQL statement, it defaults to the statement level scope. You can explicitly apply a statement level scope using the for query modifier. In both of the following examples, the statement level scope is applied to the second SQL statement, SELECT 2. All other SELECT statements are unaffected by the hint.

SELECT 1
; SELECT 
  --@dtm_hint_name=parameter_value
  2
; SELECT 3
;

Explicitly apply the statement level scope to a SQL statement using the for query modifier.

SELECT 1
; SELECT 
  --@dtm_hint_name=parameter_value for query
  2
; SELECT 3
;

Request Level Hints

To specify that a hint apply to all SQL statements in a multi-statement request (MSR), use the for request modifier.

Certain hints are only request level and higher. For example, all bulk loading hints accept only for request, for session, or application level scope. Additionally, if a hint is request level or greater in scope, failing to add the for request modifier will cause the hint to not be applied to the SQL statement.

SELECT 1
  --@dtm_hint_name=parameter_value for request
; SELECT 2
; SELECT 3
;

If you specify a hint in the middle of a multi-statement request using the for request modifier, the hint applies to all statements in the request. In the following example, the hint applies to the SELECT 1 statement as well as the SELECT 2 and SELECT 3 statements.

SELECT 1
; SELECT 
  --@dtm_hint_name=parameter_value for request
2
; SELECT 3
;

Request Level Hints and Stored Procedures

The EXEC (or EXECUTE) command is used to execute a stored procedure or macro. EXEC is equivalent to using the for request modifier.

In the following example, the for query modifier (statement level) applies the hint to the body of the the macro.

CREATE TABLE table_name (i int);
CREATE MACRO macro_name as (
    --@dtm_hint_name=parameter_value for query
    SELECT * FROM table_name
    ; SELECT * FROM table_name;
);

exec macro_name;

If you use the for request modifier (request scope), the hint you specify applies to all the statements that make up the macro's multi-statement request.

CREATE TABLE table_name (i int);
CREATE MACRO macro_name as (
    --@dtm_hint_name=parameter_value for request
    SELECT * FROM table_name
    ; SELECT * FROM table_name;
);
exec macro_name;

Session Level Hints

Session hints let you specify hints at the session scope, as opposed to passing them for each individual statement. Thus, all queries sent to the database using a given session will use the hints you provide. The hint will affect only those queries sent from the time the session hint is set. Any earlier queries in the same session are unaffected.

The for session modifier applies to the entirety of a request, and to all statements that follow. In the following example, the for session modifier is applied to the second SELECT statement (SELECT 2), and applies to all those that follow it (SELECT 3 and SELECT 4)

SELECT 1;
SELECT 2; 
SELECT 3 --@dtm_hint_name=parameter_value for session
;
SELECT 4;