Add options to Transact-SQL queries for Azure Synapse using OPTION Clause


Availability: Hyper-Q 3.39.0 and later.


The dtm_add_synapse_query_options hint lets you add options to Transact-SQL queries for Azure Synapse. You can use dtm_add_synapse_query_options in the dtm.ini configuration file or as a DTM command. Typically, you use dtm_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 dtm_add_synapse_query_options is:

-- @dtm_add_synapse_query_options = options


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.



Specify an Option for Azure Synapse Analytics

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')).



FROM tblCustomer WHERE i > 1


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


Have more questions? Submit a request


Article is closed for comments.