Skip to main content

Datometry Documentation

Add Options to Transact-SQL Queries for Azure Synapse

The add_synapse_query_options hint lets you add options to Transact-SQL queries for Azure Synapse. You can use add_synapse_query_options in the Hyper-Q configuration file (dtm.ini ) or as a Hyper-Q hint. Typically, you use add_synapse_query_options on a per request basis.

Query hints such as FORCE ORDER and USE HINT are supplied to Azure Synapse as an OPTION clause, which specifies that the indicated query hint be used throughout the entire query.

You can use the OPTION clause in SELECT, DELETE, UPDATE and MERGE statements. To learn more, see the Microsoft article OPTION Clause (Transact-SQL).

The syntax for add_synapse_query_options is:

add_synapse_query_options=options

where:

Parameter

Description

add_synapse_query_options

Hint that lets you add the OPTION clause to the resulting Azure Synapse statement.

options

The options to assign to the query.

Hint Scope

The  add_synapse_query_options hint can be specified at the request, session, and global (or application) 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 43. Specify a Transact-SQL Query as a Hyper-Q Hint per Statement

This example instructs Azure Synapse to use the FORCE ORDER hint to join the tables in the exact order specified in the SQL statement. It also specifies that the query use the legacy cardinality estimator query hint (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')).

SELECT *
--@dtm_add_synapse_query_options=FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')
FROM table_name WHERE i > 1;


Example 44. Specify a Hint for the Duration of a Session

Using the for session hint, you can specify that a hint be used for the duration of a session, as opposed to for a single query.

--@dtm_add_synapse_query_options=FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') for session;